SQL Server 動態產生資料庫所有表Insert語句

標籤：style   blog   http   io   color   os   ar   使用   for   

一、 背景

　　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語句