SQL Server dynamically generates all table INSERT statements for a database

I. BACKGROUND

SQL Server, what do we usually do if we need to put all of the table data for database A into database B? I will use the import and export function of SSMs, the import and export of table data, it is understandable, such import is very simple and convenient;

However, when we have hundreds of tables, and some of the tables have self-increment ID, then this time using SSMS, you need to manually set (1), you have to know how to set hundreds of these options is a painful thing, and in the end it is likely because of foreign key constraints caused the import and export failure.

(Fig. 1)

Although SSMS provides the ability to build SSIS packages in the final step of importing and exporting, it is not as fast and convenient as I want to be in the need to transfer data.

Naturally, I thought of the insert into XX SELECT from XX where (the benefit of this approach is that data records, fields can be controlled), but how can you quickly generate these statements for all the tables in the entire database?

If you need to batch generate the following SQL, I think this article can help you:

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

Second, the script explanation

(a) I have written a template, this template you only need to set the name of @fromdb and @todb, so that will be generated from @fromdb export all the tables inserted into the @todb SQL statement, you need to be aware of: to choose @fromdb corresponding database execution template SQL, Otherwise, the required tables and fields cannot be generated.

DECLARE @fromdb VARCHAR( -)DECLARE @todb VARCHAR( -)DECLARE @tablename VARCHAR( -)DECLARE @columnnames NVARCHAR( -)DECLARE @isidentity NVARCHAR( -)DECLARE @temsql NVARCHAR(Max)DECLARE @sql NVARCHAR(Max)SET @fromdb = 'Master'SET @todb = 'master_new'--CursorsDECLARE @itemCur CURSORSET @itemCur = CURSOR  for     SELECT '['+[name]+']'  fromSys.tablesWHEREType='U' Order  bynameOPEN @itemCurFETCH NEXT  from @itemCur  into @tablename while @ @FETCH_STATUS=0BEGIN        SET @sql = "'    --Get table field    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'    EXECsp_executesql@temsqlN'@columnnamesOUT NVARCHAR () OUTPUT',@columnnamesOUT=@columnnamesOUTPUTPRINT('--'+@tablename)    --determine if there are self-increment fields    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'    EXECsp_executesql@temsqlN'@isidentityOUT NVARCHAR () OUTPUT',@isidentityOUT=@isidentityOUTPUT--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    --return to SQL    PRINT(@sql)PRINT('GO')+CHAR( -)    FETCH NEXT  from @itemCur  into @tablenameEND CLOSE @itemCurdeallocate @itemCur

(b) The following is a partial script of the returned generated, which automatically determines whether the table has a self-increment field and generates a corresponding IDENTITY_INSERT statement if it exists.

--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 dynamically generates all table INSERT statements for a database