/*--數(shù)據(jù)庫數(shù)據(jù)復(fù)制

 將一個數(shù)據(jù)庫中的數(shù)據(jù)復(fù)制到另一個數(shù)據(jù)庫
 如果某列在目標(biāo)數(shù)據(jù)庫中為標(biāo)識列,將不會被復(fù)制

 適用范圍:數(shù)據(jù)庫結(jié)構(gòu)發(fā)生了變化,想將舊數(shù)據(jù)庫進(jìn)行升級
  這樣就可以根據(jù)新的數(shù)據(jù)庫結(jié)構(gòu)創(chuàng)建一個空庫,然后
  將舊數(shù)據(jù)庫的所有數(shù)據(jù)復(fù)制到新庫中
--*/

/*--調(diào)用示例

 exec p_copydb ‘源數(shù)據(jù)庫‘,‘目標(biāo)數(shù)據(jù)庫‘
 exec p_copydb ‘a(chǎn)cc_五醫(yī)‘,‘a(chǎn)cc_演示數(shù)據(jù)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,  --要復(fù)制數(shù)據(jù)的數(shù)據(jù)庫--源數(shù)據(jù)庫
@n_dbname sysname,  --接收數(shù)據(jù)的數(shù)據(jù)庫--目標(biāo)數(shù)據(jù)庫
@cleardb bit=0   --清空目標(biāo)數(shù)據(jù)庫
as
declare @sql nvarchar(4000)

--禁用約束,防止復(fù)制時的數(shù)據(jù)沖突
set @sql=‘declare #tbc cursor for select name,tbname=object_name(parent_obj)
 from ‘+@n_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=‘a(chǎn)lter table ‘+@n_dbname+‘..[‘+@tbname+‘] NOCHECK CONSTRAINT [‘+@name+‘]‘
 exec(@sql)
 fetch next from #tbc into @name,@tbname
end
close #tbc

--復(fù)制數(shù)據(jù)
declare @sql1 varchar(8000)
set @sql=‘declare #tb cursor for select a.name from ‘
 +@o_dbname+‘..sysobjects a inner join ‘
 +@n_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 @sql1=@sql1+‘‘,[‘‘+a.name+‘‘]‘‘ from(
  select name from ‘+@o_dbname+‘..syscolumns where id in
  (select id from ‘+@o_dbname+‘..sysobjects where name=‘‘‘+@tbname+‘‘‘)
 ) a inner join (
  select name from ‘+@n_dbname+‘..syscolumns where status<>0x80 and id in
  (select id from ‘+@n_dbname+‘..sysobjects where name=‘‘‘+@tbname+‘‘‘)
 ) 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 ‘+@n_dbname+‘..[‘+@tbname+‘](‘+@sql1
  +‘) select ‘+@sql1+‘ from ‘+@o_dbname+‘..[‘+@tbname+‘]‘)
 if @@error<>0
  print(‘insert into ‘+@n_dbname+‘..[‘+@tbname+‘](‘+@sql1
   +‘) select ‘+@sql1+‘ from ‘+@o_dbname+‘..[‘+@tbname+‘]‘)
 fetch next from #tb into @tbname
end
close #tb
deallocate #tb

--數(shù)據(jù)復(fù)制完成后啟用約束
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
 set @sql=‘a(chǎn)lter table ‘+@n_dbname+‘..[‘+@tbname+‘] CHECK CONSTRAINT [‘+@name+‘]‘
 exec(@sql)
 fetch next from #tbc into @name,@tbname
end
close #tbc
deallocate #tbc
go