How to export insert script in sqlserver
sqlserver how to export insert scripts, for this problem, this article details the corresponding analysis and solutions, hoping to help more small partners who want to solve this problem to find a simpler and easier way.
The code is as follows: DECLARE @tbImportTables table (tablename varchar(128), deleted tinyint) -- append tables which you want to import Insert Into @tbImportTables (tablename, deleted) values ('tentitytype', 1) Insert Into @tbImportTables (tablename, deleted) values ('tattribute', 1) -- append all tables --Insert Into @tbImportTables (tablename, deleted) select table_name, 1 from INFORMATION_SCHEMA.tables where table_type = 'BASE TABLE' DECLARE @tbImportScripts table (script varchar(max)) Declare @tablename varchar(128), @deleted tinyint, @columnname varchar(128), @fieldscript varchar(max), @valuescript varchar(max), @insertscript varchar(max) Declare curImportTables Cursor For Select tablename, deleted From @tbImportTables Open curImportTables Fetch Next From curImportTables Into @tablename, @deleted WHILE @@Fetch_STATUS = 0 Begin If (@deleted = 1) begin Insert into @tbImportScripts(script) values ('Truncate table ' + @tablename) end Insert into @tbImportScripts(script) values ('SET IDENTITY_INSERT ' + @tablename + ' ON') set @fieldscript = '' select @fieldscript = @fieldscript + column_name + ',' from INFORMATION_SCHEMA.columns where table_name = @tablename and data_type not in('timestamp', 'image') set @fieldscript = substring(@fieldscript, 0, len(@fieldscript)) set @valuescript = '' select @valuescript = @valuescript + 'case when ' + column_name + ' is null then ''null'' else '''''''' + convert(varchar(max), ' + column_name + ') + '''''''' end +'',''+' from INFORMATION_SCHEMA.columns where table_name = @tablename and data_type not in('timestamp', 'image') set @valuescript = substring(@valuescript, 0, len(@valuescript) - 4) set @insertscript = 'select ''insert into ' + @tablename + '(' + @fieldscript + ') values(' + '''+' + @valuescript + ' + '')'' from ' + @tablename Insert into @tbImportScripts(script) exec ( @insertscript) Insert into @tbImportScripts(script) values ('SET IDENTITY_INSERT ' + @tablename + ' OFF') Insert into @tbImportScripts(script) values ('GO ') Fetch Next From curImportTables Into @tablename, @deleted End Close curImportTables Deallocate curImportTables Select * from @tbImportScripts
About sqlserver how to export the solution to the problem of inserting scripts to share here, I hope the above content can have some help for everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.
