数据|数据库

/*--数据库数据复制

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

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

/*--调用示例

 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 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 ] NOCHECK CONSTRAINT ['+name+']'
 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 '
  a inner join '
  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 sql1=sql1+'',[''+a.name+'']'' from(
  select name from where id in
  (select id from where )
 ) a inner join (
  select name from where status<>0x80 and id in
  (select id from where )
 ) 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
  +') select from )
 if error<>0
  print('insert into
   +') select from )
 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 ] CHECK CONSTRAINT ['+name+']'
 exec(sql)
 fetch next from #tbc into name,tbname
end
close #tbc
deallocate #tbc
go