SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tatsu / Script Table Data to Insert Statements / Latest Posts

RE: Script Table Data to Insert Statements

SQL_ME_RICH — Fri, 13 Apr 2012 17:11:24 GMT

Tried this script on a 2005 SP3 system, to try and construct insert statements for a rather large table of data (hundreds of millions of rows). Needless to say - it didn't work, and through and out of memory exception error in the messages section of the results at about 33 million. :-/

RE: Script Table Data to Insert Statements

David Masciangelo — Thu, 15 Jul 2010 11:45:12 GMT

[quote][b]titan2782 (6/10/2010)[/b][hr]Doesnt work. I just get a rowset of n rows (n being the total records in the target table) of an insert statement with no columns and no data.[/quote]I had the same problem, don't use the [i]schemaName.tableName[/i] format, just use the table name.

RE: Script Table Data to Insert Statements

titan2782 — Thu, 10 Jun 2010 16:23:34 GMT

Doesnt work. I just get a rowset of n rows (n being the total records in the target table) of an insert statement with no columns and no data.

RE: Script Table Data to Insert Statements

lifuhai — Fri, 30 Oct 2009 19:00:33 GMT

Hi guys, I think this is pretty useful when we dont have trust connection to other server and want to transfer some bunch of data to there. actually I updated the collation stuff, and all the others kind of we may have same or similar updates. hope some guy can finish the to do list I stated in the scripts.the scripts is here:[code="plain"]/*Use this script to create insert statements for each row in the specified table.Instructions:1. Set the database you want to script from as normal.2. change the set @TableName = '<YourTableName>' line to be thetable you want to script out.3. Run the script and copy all the text from the results belowthe line with all the dashes (----).Notes: If you get the error message "Invalid object name '<YourTableName>'." then you either forgot to set the correct database or you spelled your table name wrongCredits: Bob Wiechman - Fix for smalldatetime support Richard Lesh - correct support of uniqueidentifiers, automatic setting of Identity off/on, add Where clause support, more detail in debug mode. Fuhai Li - correct support of null values; fix the coallation conflicts via using default coallation correct the identity insert on/off for non debug mode column name with bracket to make it robust for naming To do: test the null values for gui type To do: add on parameters to choose which fields would export via colorder such as 1,2,... */declare @TableName sysnamedeclare @WhereClause varchar(1024)declare @IdentityInsert intdeclare @ColName sysnamedeclare @ColType tinyintdeclare @ColStatus tinyintdeclare @DebugMode bitdeclare @ColList nvarchar(4000)declare @ValList nvarchar(4000)declare @SQL1 nvarchar(1000)declare @SQL2 nchar(10)declare @SQL3 nchar(1000)set @TableName = 'Region' -- '<YourTableName>' set @WhereClause = '' -- limit scope of inserts, this will be hard coded thing to narrow down the setset @DebugMode = 0 -- set to 1 if you only want a scriptset @IdentityInsert = 0 -- set to 1 if you want to force IDENTITY_INSERT statementsset @ColList = ''set @ValList = ''set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('set @SQL2 = ') values ('set @SQL3 = ')'' COLLATE DATABASE_DEFAULT, ''''''null'''''', ''null'') from ' + @TableNameif @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3declare csrColumns cursor local fast_forward for select c.name, c.xtype, c.status from syscolumns c inner join sysobjects o on o.id = c.id where o.name = @TableName and o.xtype in ('U', 'S') order by ColIDopen csrColumnsfetch next from csrColumns into @ColName, @ColType, @ColStatus/*while @@fetch_status = 0begin set @ColList = @ColList + ' ' + @ColName if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet) set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+''' else if @ColType in (175, 239, 231, 231, 167) -- uid and string types set @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+''''''' else if @ColType in (58, 61) -- dates (nulls not supported yet) set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+''''''' else if @ColType = 36 -- uniqueidentfiers (nulls not supported yet) set @ValList = @ValList + ' ''''{''+convert(varchar(200),' + @ColName + ')+''}''''' if @DebugMode = 1 begin print '-- @ValList: ' + rtrim(@ValList) end if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute fetch next from csrColumns into @ColName, @ColType, @ColStatusend*/while @@fetch_status = 0begin --set @ColList = @ColList + ' ' + @ColName set @ColList = @ColList + ' ' + '['+ @ColName + ']' if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types set @ValList = @ValList + ' ''+isnull(convert(varchar(200),' + @ColName + '),''null'')+''' else if @ColType in (175, 239, 231, 231, 167) -- uid and string types set @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+''''''' else if @ColType in (58, 61, 36) -- dates and uniqueidentfiers set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),' + @ColName + '),''null'')+''''''' if @DebugMode = 1 begin print '-- @ValList: ' + @ValList end if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute fetch next from csrColumns into @ColName, @ColType, @ColStatusendclose csrColumnsdeallocate csrColumnsset @ColList = replace(ltrim(@ColList), ' ', ', ')set @ValList = replace(ltrim(@ValList), ' ', ', ')if @IdentityInsert = 1 and @DebugMode = 1 print 'set identity_insert ' + @TableName + ' on'if @IdentityInsert = 1 and @DebugMode = 0 select 'set identity_insert ' + @TableName + ' on'if @DebugMode = 1 print @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClauseelse exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)if @IdentityInsert = 1 and @DebugMode = 1 print 'set identity_insert ' + @TableName + ' off'if @IdentityInsert = 1 and @DebugMode = 0 select 'set identity_insert ' + @TableName + ' off'[/code]Regards,Fuhai

RE: Script Table Data to Insert Statements

LAJ — Fri, 04 Sep 2009 15:40:20 GMT

To handle nulls, I changed the code to the following:OLD CODE[code]set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''[/code]NEW CODE[code]set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''[/code]I actually converted the script to a stored procedure, I probably wouldn't recommend this in a production environment, but for development it is okay.[code]CREATE PROCEDURE [dbo].[Script_Table] ( @TableName sysname, @IdentityInsert int = 0, @DebugMode bit = 0)ASSET NOCOUNT ON --declare @TableName sysnamedeclare @WhereClause varchar(1024)--declare @IdentityInsert intdeclare @ColName sysnamedeclare @ColType tinyintdeclare @ColStatus tinyint--declare @DebugMode bitdeclare @ColList varchar(8000)declare @ValList varchar(8000)declare @SQL1 nvarchar(4000)declare @SQL2 nchar(10)declare @SQL3 nchar(1000)--set @TableName = 'emp' -- '' set @WhereClause = '' -- limit scope of inserts--set @DebugMode = 0 -- set to 1 if you only want a script--set @IdentityInsert = 1 -- set to 1 if you want to force IDENTITY_INSERT statementsset @ColList = ''set @ValList = ''set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('set @SQL2 = ') values ('set @SQL3 = ')'', ''''''null'''''', ''null'') from ' + @TableNameif @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3/* select c.name, c.xtype, c.status from syscolumns c inner join sysobjects o on o.id = c.id where o.name in (@TableName) and o.xtype in ('U', 'S') order by ColID*/declare csrColumns cursor local fast_forward for select c.name, c.xtype, c.status from syscolumns c inner join sysobjects o on o.id = c.id where o.name = @tablename and o.xtype in ('U', 'S') order by ColIDopen csrColumnsfetch next from csrColumns into @ColName, @ColType, @ColStatuswhile @@fetch_status = 0begin if not ((@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0)) set @ColList = @ColList + ' ' + @ColName if @ColType in (127,173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet) if(@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0) set @ValList = @ValList else set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+''''''' else if @ColType in (175, 239, 231, 231, 167) -- uid and string types set @ValList = @ValList + ' ''''''+isnull(replace([' + @ColName + '],'''''''',''''''''''''),''null'')+''''''' else if @ColType in (36,58, 61) -- dates , uniqueidentifiers(nulls not supported yet) set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+''''''' if @DebugMode = 1 begin print '-- @ValList: ' + rtrim(@ValList) end if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute fetch next from csrColumns into @ColName, @ColType, @ColStatusendclose csrColumnsdeallocate csrColumnsset @ColList = replace(ltrim(@ColList), ' ', ', ')set @ValList = replace(ltrim(@ValList), ' ', ', ')if @IdentityInsert = 1 print 'set identity_insert ' + @TableName + ' on'if @DebugMode = 1 print + @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClauseelse exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)if @IdentityInsert = 1 print 'set identity_insert ' + @TableName + ' off'SET NOCOUNT OFFGO[/code]

RE: Script Table Data to Insert Statements

kermitgreen — Fri, 07 Aug 2009 04:58:03 GMT

Brilliant solution to scripting table data. I ran into trouble with one column name "%Target" so, I added square brackets around @ColName and that fixed my issue.begin set @ColList = @ColList + ' ' + '['+ @ColName + ']' if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet) set @ValList = @ValList + ' ''+convert(varchar(200),' + '[' + @ColName + ']' + ')+''' else if @ColType in (175, 239, 231, 231, 167) -- uid and string types set @ValList = @ValList + ' ''''''+isnull(' + '[' + @ColName + ']' + ',''null'')+''''''' else if @ColType in (58, 61) -- dates (nulls not supported yet) set @ValList = @ValList + ' ''''''+convert(varchar(200),' + '[' + @ColName +']' + ')+''''''' else if @ColType = 36 -- uniqueidentfiers (nulls not supported yet) set @ValList = @ValList + ' ''''{''+convert(varchar(200),' + '[' + @ColName + ']' + ')+''}''''' if @DebugMode = 1 begin print '-- @ValList: ' + rtrim(@ValList) end if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute fetch next from csrColumns into @ColName, @ColType, @ColStatusendThanks,Johnny

RE: Script Table Data to Insert Statements

Furby — Fri, 20 Feb 2009 09:11:02 GMT

Many thanks for the script.For anyone interested - In order to get the datetime stamps to return seconds (and milliseconds) I had to change the following:[i] else if @ColType in (58, 61) -- dates (nulls not supported yet) set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+'''''''[/i]TO[i] else if @ColType in (58, 61) -- dates (nulls not supported yet) set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + '[b],13[/b])+'''''''[/i]Of course any CONVERT style code can be used.

RE: Script Table Data to Insert Statements

devavejay-806985 — Thu, 12 Feb 2009 12:45:16 GMT

This was awesome once i figured out i had to drop the schema.table name syntax from select c.name, c.xtype, c.status from syscolumns c inner join sysobjects o on o.id = c.id where o.name = 'CalendarCountry' and o.xtype in ('U', 'S') order by ColIDjust a fyi

RE: Script Table Data to Insert Statements

kuldipMCA — Tue, 20 Jan 2009 07:11:47 GMT

This is NIce script but when ever NULL comes it's not workingand we need GO at the end of the every insert statementso please add the go statement and send me on my mail idkuldip.bhatt@digi-corp.comi am not DBA but i want Learn this type script if any send me and i am also get Help from your side

RE: Script Table Data to Insert Statements

Alexander Zeitler — Tue, 30 Sep 2008 14:38:44 GMT

Hi,should this script also work with SQL Server 2000 databases?I always get the error 'invalid object name'.Alex

Script Table Data to Insert Statements

Tatsu — Fri, 28 Sep 2007 14:46:00 GMT

Comments posted to this topic are about the item [B]Script Table Data to Insert Statements[/B]