转:http://blog.csdn.net/zjcxc/archive/2003/12/29/20081.aspx
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
/**/ /*--数据库数据复制
将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制
适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--邹建 2003.10(引用请保留此信息)--*/
/**/ /*--调用示例
exec p_copydb 'bns_aa','bns_new'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/
create proc p_copydb
@o_dbname sysname, -- 要复制数据的数据库--源数据库
@n_dbname sysname -- 接收数据的数据库--目标数据库
as
declare @sql nvarchar ( 4000 )
-- 禁用约束/触发器,防止复制时的数据冲突
set @sql = ' declare #tbc cursor for select name
from ' + @n_dbname + ' ..sysobjects where xtype= '' U '' and status>=0 '
exec ( @sql )
declare @tbname sysname
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status = 0
begin
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] NOCHECK CONSTRAINT ALL '
exec ( @sql )
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] disable trigger ALL '
exec ( @sql )
fetch next from #tbc into @tbname
end
close #tbc
-- 复制数据
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
-- 数据复制完成后启用约束
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status = 0
begin
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] CHECK CONSTRAINT ALL '
exec ( @sql )
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] enable trigger ALL '
exec ( @sql )
fetch next from #tbc into @tbname
end
close #tbc
deallocate #tbc
go
drop procedure [ dbo ] . [ p_copydb ]
GO
/**/ /*--数据库数据复制
将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制
适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--邹建 2003.10(引用请保留此信息)--*/
/**/ /*--调用示例
exec p_copydb 'bns_aa','bns_new'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/
create proc p_copydb
@o_dbname sysname, -- 要复制数据的数据库--源数据库
@n_dbname sysname -- 接收数据的数据库--目标数据库
as
declare @sql nvarchar ( 4000 )
-- 禁用约束/触发器,防止复制时的数据冲突
set @sql = ' declare #tbc cursor for select name
from ' + @n_dbname + ' ..sysobjects where xtype= '' U '' and status>=0 '
exec ( @sql )
declare @tbname sysname
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status = 0
begin
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] NOCHECK CONSTRAINT ALL '
exec ( @sql )
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] disable trigger ALL '
exec ( @sql )
fetch next from #tbc into @tbname
end
close #tbc
-- 复制数据
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
-- 数据复制完成后启用约束
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status = 0
begin
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] CHECK CONSTRAINT ALL '
exec ( @sql )
set @sql = ' alter table ' + @n_dbname + ' ..[ ' + @tbname + ' ] enable trigger ALL '
exec ( @sql )
fetch next from #tbc into @tbname
end
close #tbc
deallocate #tbc
go