SqlServer批量Sql一个表的数据导入到另一个数据

一个表的导入：

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