SQL Server 动态生成数据库所有表Insert语句

SQL Server 动态生成数据库所有表Insert语句

  一、 背景
  SQL Server,如果我们需要把数据库A的所有表数据到数据库B中,通常我们会怎么做呢?我会使用SSMS的导入导出功能,进行表数据的导入导出,无可厚非,这样的导入非常简单和方便;
  但是,当我们的表有上百个,而且有些表是有自增ID的,那么这个时候使用SSMS的话,你需要一个个手动设置(如图1),你要知道,需要设置上百个的这些选项是件多么痛苦的事情,而且最后很可能会因为外键约束导致导入导出失败。
  (图1)
  虽然SSMS在导入导出的最后一步提供了生成SSIS包的功能,但是对于转移数据的需求来说,还是无法达到我想要的快速、方便。
  自然而然,我想到了INSERT INTO XX SELECT FROM XX WHERE这样的方式(这种方式的好处就是可以对数据记录、字段进行控制),但是如何才能快速生成整个数据库所有表的这些语句呢?
  假如你需要批量生成下面的SQL,我想这篇文章就可以帮到你了:
  --[OpinionList]
  SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] ON
  INSERT INTO [master_new].[dbo].[OpinionList](Id,Batch,LinkId,DB_Names,CreateTime)
  SELECT * FROM [DBA_DB].[dbo].[OpinionList]
  SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] OFF
  GO
  二、 脚本解释
  (一) 我编写了一个模板,这个模板你只需要设置@fromdb和@todb的名称,这样就会生成从@fromdb导出所有表插入到@todb中的SQL语句,需要注意的是:要选择@fromdb对应的数
据库执行模板SQL,不然无法生成需要的表和字段。
DECLARE @fromdb VARCHAR(100)
DECLARE @todb VARCHAR(100)
DECLARE @tablename VARCHAR(100)
DECLARE @columnnames NVARCHAR(300)
DECLARE @isidentity NVARCHAR(30)
DECLARE @temsql NVARCHAR(max)
DECLARE @sql NVARCHAR(max)
SET @fromdb = ‘master‘
SET @todb = ‘master_new‘
--游标
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR
SELECT ‘[‘+[name]+‘]‘ from sys.tables WHERE type=‘U‘ order by name
OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @tablename
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = ‘‘
--获取表字段
SET @temsql = N‘
BEGIN
SET @columnnamesOUT =‘‘‘‘
SELECT @columnnamesOUT = @columnnamesOUT + ‘‘,‘‘ + name
From sys.columns where object_id=OBJECT_ID(‘‘[‘+@fromdb+‘].dbo.‘+@tablename+‘‘‘)
order by column_id
SELECT @columnnamesOUT=substring(@columnnamesOUT,2,len(@columnnamesOUT))
END
EXEC sp_executesql @temsql,N‘@columnnamesOUT NVARCHAR(300) OUTPUT‘,@columnnamesOUT=@columnnames OUTPUT
PRINT (‘--‘+@tablename)
--判断是否有自增字段
SET @temsql = N‘
BEGIN
SET @isidentityOUT =‘‘‘‘
SELECT @isidentityOUT = name
From sys.columns where object_id=OBJECT_ID(‘‘[‘+@fromdb+‘].dbo.‘+@tablename+‘‘‘)
and is_identity = 1
END
EXEC sp_executesql @temsql,N‘@isidentityOUT NVARCHAR(30) OUTPUT‘,@isidentityOUT=@isidentity OUTPUT
--IDENTITY_INSERT ON
IF @isidentity != ‘‘
BEGIN
SET @sql = ‘SET IDENTITY_INSERT [‘+@todb+‘].[dbo].[‘+@tablename+‘] ON
END
--INSERT
SET @sql = @sql+‘INSERT INTO [‘+@todb+‘].[dbo].[‘+@tablename+‘](‘+@columnnames+‘)
SELECT * FROM [‘+@fromdb+‘].[dbo].[‘+@tablename+‘]‘
--IDENTITY_INSERT OFF
IF @isidentity != ‘‘
BEGIN
SET @sql = @sql+‘
SET IDENTITY_INSERT [‘+@todb+‘].[dbo].[‘+@tablename+‘] OFF‘
END
--返回SQL
PRINT(@sql)PRINT(‘GO‘)+CHAR(13)
FETCH NEXT FROM @itemCur INTO @tablename
END
CLOSE @itemCur
DEALLOCATE @itemCur
  (二) 下面就是返回的生成的部分脚本,模板会自动判断表是否存在自增字段,如果存在就会生成对应的IDENTITY_INSERT语句。
--spt_values
INSERT INTO [master_new].[dbo].[spt_values](name,number,type,low,high,status)
SELECT * FROM [master].[dbo].[spt_values]
GO
--[OpinionList]
SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] ON
INSERT INTO [master_new].[dbo].[OpinionList](Id,Batch,LinkId,DB_Names,CreateTime)
SELECT * FROM [DBA_DB].[dbo].[OpinionList]
SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] OFF
GO

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。