SQL Server dynamically generates all table INSERT statements for a database

SQL Server dynamically generates all table INSERT statements for a database

2014-10-31 10:27 by listening to the wind blowing rain, 2757 reading, 2 reviews, Favorites, compilation

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 (300  ) DECLARE @isidentity NVARCHAR () DECLARE @temsql NVARCHAR (max) DECLARE @sql NVARCHAR (max) Set @fromdb = ' master ' Set @todb =  ' Master_new '--cursor declare @itemCur cursorset @itemCur = cursor for SELECT ' [' +[name]+ '] ' from sys.tables WHERE type= ' U ' ORDER by Nameopen @itemCurFETCH NEXT from @itemCur into @tablenameWHILE @ @FETCH_STATUS =0begin SET @sql = "--Received     Take table field Set @temsql = N ' BEGIN set @columnnamesOUT = ' ' SELECT @columnnamesOUT = @columnnamesOUT + ', ' + name From Sys.columns where object_id=object_id (' [' ['] [email protected]+ '].dbo. ' [email protected]+ ') Order by column_id SELECT @columnnamesOUT =substring (@columnnamesOUT, 2,len (@columnnamesO UT) END ' EXEC sp_executesql @temsql, N ' @columnnamesOUT NVARCHAR (+) output ', @[email protected] output P  RINT ('---' [email protected])--Determine if there is a self-increment field SET @temsql = N '  BEGIN SET @isidentityOUT = "" SELECT @isidentityOUT = name from sys.columns where object_id=object_id (' [' [em] ail protected]+ '].dbo. '  [email protected]+ ') and is_identity = 1 END ' EXEC sp_executesql @temsql, N ' @isidentityOUT NVARCHAR (30) Output ', @[email protected] output--identity_insert on IF @isidentity! = ' BEGIN Set @sql = ' Set ID Entity_insert [' [email protected]+ ']. [dbo]. [' [email protected]+ '] On ' END--insert SET @sql = @sql + ' INSERT into [' [email protected]+ ']. [dbo]. [' [email protected]+ '] (' [email protected]+ ') SELECT * FROM [' [email protected]+ ']. [dbo]. [' [email protected]+ '] '--identity_insert OFF IF @isidentity! = ' BEGIN Set @sql = @sql + ' Set IdentIT Y_insert [' [email protected]+ ']. [dbo]. [' [email protected]+ '] OFF ' END-Returns the SQL print (@sql) print (' GO ') +char FETCH NEXT from the @itemCur into @tablenameEND CLOSE @itemCurDEA Llocate @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