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.

650) this.width=650; "title=" wpsfa22.tmp "src=" Http://images.cnitblog.com/blog/48305/201410/311027131283506.png " alt= "Wpsfa22.tmp" width= "505" height= "253" border= "0" style= "border:0px;background-image:none;padding-left:0px; padding-right:0px;padding-top:0px; "/>

(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:

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

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

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

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.

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

declare  @fromdb  varchar (+) declare  @todb  varchar (+) declare  @tablename  varchar (100) declare  @columnnames  nvarchar () declare  @isidentity  nvarchar (declare ) @temsql  nvarchar (max) declare  @sql  nvarchar (max) set  @fromdb  =  ' master ' set  @todb  =  ' master_new '--cursors 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  =   '     --get table fields     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 (@columnnamesOUT))     END      '     EXEC sp_executesql  @temsql, N ' @columnnamesOUT  nvarchar (300)  output ',@[email protected] output    print  ('---' [email protected ])     --Determine if there are self-increment fields     SET  @temsql  = n '      BEGIN    SET  @isidentityOUT  = ' '     select @ Isidentityout = name     from sys.columns where object_id= OBJECT_ID (' [' [email protected]+ ']].dbo. ' [Email protected]+ ')     and is_identity = 1    END      '     EXEC sp_executesql  @temsql, N ' @isidentityOUT  nvarchar (+)   OUTPUT ',@[email protected] output    --identity_insert on     IF  @isidentity  !=  '     BEGIN         SET  @sql  =  ' set identity_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  !=  ' &Nbsp;   begin        set  @sql  =  @sql + ' SET  identity_insert [' [email protected]+ ']. [dbo]. [' [email protected]+ '] off '     end    --return sql     print (@sql) PRINT (' GO ') +char     FETCH NEXT FROM  @itemCur  INTO  @tablenameEND  CLOSE  @itemCurDEALLOCATE   @itemCur

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

(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.

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

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

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

SQL Server dynamically generates all table INSERT statements for a database