SQL Server 动态生成数据库所有表Insert语句

--[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

二、 脚本解释

(一) 我编写了一个模板，这个模板你只需要设置@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 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

(二) 下面就是返回的生成的部分脚本，模板会自动判断表是否存在自增字段，如果存在就会生成对应的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

SQL Server 动态生成数据库所有表Insert语句

标签：