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