一、 背景
SQL Server，如果我們需要把數(shù)據(jù)庫A的所有表數(shù)據(jù)到數(shù)據(jù)庫B中，通常我們會怎么做呢？我會使用SSMS的導(dǎo)入導(dǎo)出功能，進(jìn)行表數(shù)據(jù)的導(dǎo)入導(dǎo)出，無可厚非，這樣的導(dǎo)入非常簡單和方便；
但是，當(dāng)我們的表有上百個(gè)，而且有些表是有自增ID的，那么這個(gè)時(shí)候使用SSMS的話，你需要一個(gè)個(gè)手動(dòng)設(shè)置（如圖1），你要知道，需要設(shè)置上百個(gè)的這些選項(xiàng)是件多么痛苦的事情，而且最后很可能會因?yàn)橥怄I約束導(dǎo)致導(dǎo)入導(dǎo)出失敗。
（圖1）
雖然SSMS在導(dǎo)入導(dǎo)出的最后一步提供了生成SSIS包的功能，但是對于轉(zhuǎn)移數(shù)據(jù)的需求來說，還是無法達(dá)到我想要的快速、方便。
自然而然，我想到了INSERT INTO XX SELECT FROM XX WHERE這樣的方式（這種方式的好處就是可以對數(shù)據(jù)記錄、字段進(jìn)行控制），但是如何才能快速生成整個(gè)
數(shù)據(jù)庫所有表的這些語句呢？
假如你需要批量生成下面的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
二、 腳本解釋
(一) 我編寫了一個(gè)模板，這個(gè)模板你只需要設(shè)置@fromdb和@todb的名稱，這樣就會生成從@fromdb導(dǎo)出所有表插入到@todb中的SQL語句，需要注意的是：要選擇@fromdb對應(yīng)的數(shù)
據(jù)庫執(zhí)行模板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' --游標(biāo) 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 |
(二) 下面就是返回的生成的部分腳本，模板會自動(dòng)判斷表是否存在自增字段，如果存在就會生成對應(yīng)的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 |
English » | | | | | | | | |
Text-to-speech function is limited to 100 characters