首页 > 代码库 > 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] ONINSERT INTO [master_new].[dbo].[OpinionList](Id,Batch,LinkId,DB_Names,CreateTime)SELECT * FROM [DBA_DB].[dbo].[OpinionList]SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] OFFGO
二、 脚本解释
(一) 我编写了一个模板，这个模板你只需要设置@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 CURSORSET @itemCur = CURSOR FOR SELECT ‘[‘+[name]+‘]‘ from sys.tables WHERE type=‘U‘ order by nameOPEN @itemCurFETCH NEXT FROM @itemCur INTO @tablenameWHILE @@FETCH_STATUS=0BEGIN 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 @tablenameEND CLOSE @itemCurDEALLOCATE @itemCur
(二) 下面就是返回的生成的部分脚本，模板会自动判断表是否存在自增字段，如果存在就会生成对应的IDENTITY_INSERT语句。
--spt_valuesINSERT 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] ONINSERT INTO [master_new].[dbo].[OpinionList](Id,Batch,LinkId,DB_Names,CreateTime)SELECT * FROM [DBA_DB].[dbo].[OpinionList]SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] OFFGO
SQL Server 动态生成数据库所有表Insert语句
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。