• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

数据库复制_sqlserver

sqlserver 搞代码 3年前 (2018-06-16) 66次浏览 已收录 0个评论

/*–数据库数据复制

 将一个数据库中的数据复制到另一个数据库
 如果某列在目标数据库中为标识列,将不会被复制

http://www.gaodaima.com/35462.html数据库复制_sqlserver

 适用范围:数据库结构发生了变化,想将旧数据库进行升级
  这样就可以根据新的数据库结构创建一个空库,然后
  将旧数据库的所有数据复制到新库中
–*/

/*–调用示例

 exec p_copydb ‘源数据库’,’目标数据库’
 exec p_copydb ‘acc_五医’,’acc_演示数据8′
–*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[p_copydb]
GO

create proc p_copydb
@o_dbname sysname,  –要复制数据的数据库–源数据库
@n_dbname sysname,  –接收数据的数据库–目标数据库
@cleardb bit=0   –清空目标数据库
as
declare @sql nvarchar(4000)

–禁用约束,防止复制时的数据冲突
set @sql=’declare #tbc cursor for select name,tbname=object_name(parent_obj)
 from ‘[email protected]_dbname+’..sysobjects where xtype in(”C”,”F”)’
exec(@sql)
declare @name sysname,@tbname sysname
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
 set @sql=’alter table ‘[email protected]_dbname+’..[‘[email protected]+’] NOCHECK CONSTRAINT [‘[email protected]+’]’
 exec(@sql)
 fetch next from #tbc into @name,@tbname
end
close #tbc

–复制数据
declare @sql1 varchar(8000)
set @sql=’declare #tb cursor for select a.name from ‘
 [email protected]_dbname+’..sysobjects a inner join ‘
 [email protected]_dbname+’..sysobjects b on a.name=b.name
  where a.xtype=”U” and b.xtype=”U”’
exec(@sql)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
 select @sql1=”
  ,@sql=’select @[email protected]+”,[”+a.name+”]” from(
  select name from ‘[email protected]_dbname+’..syscolumns where id in
  (select id from ‘[email protected]_dbname+’..sysobjects where name=”’[email protected]+”’)
 ) a inner join (
  select name from ‘[email protected]_dbname+’..syscolumns where status<>0x80 and id in
  (select id from ‘[email protected]_dbname+’..sysobjects where name=”’[email protected]+”’)
 ) b on a.name=b.name’
 exec sp_executesql @sql,N’@sql1 nvarchar(4000) out’,@sql1 out

 select @sql1=substring(@sql1,2,8000)
 exec(‘insert into ‘[email protected]_dbname+’..[‘[email protected]+’](‘[email protected]
  +’) select ‘[email protected]+’ from ‘[email protected]_dbname+’..[‘[email protected]+’]’)
 if @@error<>0
  print(‘insert into ‘[email protected]_dbname+’..[‘[email protected]+’](‘[email protected]
   +’) select ‘[email protected]+’ from ‘[email protected]_dbname+’..[‘[email protected]+’]’)
 fetch next from #tb into @tbname
end
close #tb
deallocate #tb

–数据复制完成后启用约束
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
 set @sql=’alter table ‘[email protected]_dbname+’..[‘[email protected]+’] CHECK CONSTRAINT [‘[email protected]+’]’
 exec(@sql)
 fetch next from #tbc into @name,@tbname
end
close #tbc
deallocate #tbc
go

欢迎大家阅读《数据库复制_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址