一个表的导入：
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
多个表的导入：
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 = 'sss'
SET @todb = 'ss' --游标
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 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
生成的部分脚本如下：
--[PosSetInfo]
INSERT INTO [ss].[dbo].[[PosSetInfo]](ID,PosSet_x,PosSet_y,PosTime)
SELECT * FROM [sss].[dbo].[[PosSetInfo]]
GO --[testdelete]
SET IDENTITY_INSERT [ss].[dbo].[[testdelete]] ON
INSERT INTO [ss].[dbo].[[testdelete]](id,NAME,dt)
SELECT * FROM [sss].[dbo].[[testdelete]]
SET IDENTITY_INSERT [ss].[dbo].[[testdelete]] OFF
GO --[TestRows2Columns]
SET IDENTITY_INSERT [ss].[dbo].[[TestRows2Columns]] ON
INSERT INTO [ss].[dbo].[[TestRows2Columns]](Id,UserName,Subject,Source)
SELECT * FROM [sss].[dbo].[[TestRows2Columns]]
SET IDENTITY_INSERT [ss].[dbo].[[TestRows2Columns]] OFF
GO --[VisiteLog]
SET IDENTITY_INSERT [ss].[dbo].[[VisiteLog]] ON --自增
INSERT INTO [ss].[dbo].[[VisiteLog]](Id,PosSet)
SELECT * FROM [sss].[dbo].[[VisiteLog]]
SET IDENTITY_INSERT [ss].[dbo].[[VisiteLog]] OFF
GO