本文共 1206 字，大约阅读时间需要 4 分钟。

; with tb1 as ( select a.name as sch,b.name as tbl,c.name as col  from sys.schemas a ,sys.all_objects b ,sys.all_columns c  where a.schema_id=b.schema_id and b.type='u'  and a.name like 'hist%' and b.object_id=c.object_id), tb2 as (select distinct sch,tbl from tb1) select sch,tbl, ' begin tran  begin try set identity_insert '+quotename('hist_'+db_name())+'.'+quotename(sch)+'.'+quotename(tbl)+' on; insert into '+quotename('hist_'+db_name())+'.'+quotename(sch)+'.'+quotename(tbl)+' ( '+stuff((select ','+quotename(col) from tb1 b where b.sch=a.sch and b.tbl=a.tbl for xml path('')),1,1,'') +' ) select '+stuff((select ','+quotename(col) from tb1 b where b.sch=a.sch and b.tbl=a.tbl for xml path('')),1,1,'') +' from '+quotename(db_name())+'.'+quotename(sch)+'.'+quotename(tbl)+' ; set identity_insert '+quotename('hist_'+db_name())+'.'+quotename(sch)+'.'+quotename(tbl)+' off; drop table '+quotename(db_name())+'.'+quotename(sch)+'.'+quotename(tbl)+'; commit tran  print '''+quotename(db_name())+'.'+quotename(sch)+'.'+quotename(tbl)+'迁移成功！'' end try begin catch  print '''+quotename(db_name())+'.'+quotename(sch)+'.'+quotename(tbl)+'迁移失败！''+error_message() rollback tran print '' 已回滚'' end catch '  from tb2 a;  

转载地址：http://nofmi.baihongyu.com/