September 28, 2007 at 2:46 pm
Comments posted to this topic are about the item Script Table Data to Insert Statements
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
September 30, 2008 at 2:38 pm
Hi,
should this script also work with SQL Server 2000 databases?
I always get the error 'invalid object name'.
Alex
January 20, 2009 at 7:11 am
This is NIce script but when ever NULL comes it's not working
and we need GO at the end of the every insert statement
so please add the go statement and send me on my mail id
i am not DBA but i want Learn this type script if any send me
and i am also get Help from your side
February 12, 2009 at 12:45 pm
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 ColID
just a fyi
February 20, 2009 at 9:11 am
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:
else if @ColType in (58, 61) -- dates (nulls not supported yet)
set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+'''''''
TO
else if @ColType in (58, 61) -- dates (nulls not supported yet)
set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ',13)+'''''''
Of course any CONVERT style code can be used.
August 7, 2009 at 4:58 am
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, @ColStatus
end
Thanks,
Johnny
September 4, 2009 at 3:40 pm
To handle nulls, I changed the code to the following:
OLD CODE
set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''
NEW CODE
set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''
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.
CREATE PROCEDURE [dbo].[Script_Table]
(
@TableName sysname,
@IdentityInsert int = 0,
@DebugMode bit = 0
)
AS
SET NOCOUNT ON
--declare @TableName sysname
declare @WhereClause varchar(1024)
--declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @ColStatus tinyint
--declare @DebugMode bit
declare @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 statements
set @ColList = ''
set @ValList = ''
set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('
set @SQL2 = ') values ('
set @SQL3 = ')'', ''''''null'''''', ''null'') from ' + @TableName
if @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 ColID
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus
while @@fetch_status = 0
begin
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, @ColStatus
end
close csrColumns
deallocate csrColumns
set @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 + ' ' + @WhereClause
else
exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)
if @IdentityInsert = 1
print 'set identity_insert ' + @TableName + ' off'
SET NOCOUNT OFF
GO
October 30, 2009 at 7:00 pm
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:
/*
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 the
table you want to script out.
3. Run the script and copy all the text from the results below
the 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 wrong
Credits:
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 sysname
declare @WhereClause varchar(1024)
declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @ColStatus tinyint
declare @DebugMode bit
declare @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 set
set @DebugMode = 0 -- set to 1 if you only want a script
set @IdentityInsert = 0 -- set to 1 if you want to force IDENTITY_INSERT statements
set @ColList = ''
set @ValList = ''
set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('
set @SQL2 = ') values ('
set @SQL3 = ')'' COLLATE DATABASE_DEFAULT, ''''''null'''''', ''null'') from ' + @TableName
if @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3
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 ColID
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus
/*
while @@fetch_status = 0
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, @ColStatus
end
*/
while @@fetch_status = 0
begin
--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, @ColStatus
end
close csrColumns
deallocate csrColumns
set @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 + ' ' + @WhereClause
else
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'
Regards,
Fuhai
June 10, 2010 at 4:23 pm
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.
July 15, 2010 at 11:45 am
titan2782 (6/10/2010)
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.
I had the same problem, don't use the schemaName.tableName format, just use the table name.
April 13, 2012 at 5:11 pm
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. :-/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply