Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script Table Data to Insert Statements Expand / Collapse
Author
Message
Posted Friday, September 28, 2007 2:46 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:01 PM
Points: 295, Visits: 287
Comments posted to this topic are about the item Script Table Data to Insert Statements

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #404250
Posted Tuesday, September 30, 2008 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2008 4:24 AM
Points: 1, Visits: 3
Hi,

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

Alex



Post #578680
Posted Tuesday, January 20, 2009 7:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 4:40 AM
Points: 22, Visits: 119
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
kuldip.bhatt@digi-corp.com

i am not DBA but i want Learn this type script if any send me
and i am also get Help from your side
Post #639931
Posted Thursday, February 12, 2009 12:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 10, 2009 7:44 AM
Points: 1, Visits: 8
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

Post #656029
Posted Friday, February 20, 2009 9:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 9:13 AM
Points: 1, Visits: 6
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.
Post #661406
Posted Friday, August 7, 2009 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 7, 2009 4:48 AM
Points: 1, Visits: 6
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
Post #766823
Posted Friday, September 4, 2009 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 6:53 PM
Points: 1, Visits: 12
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' -- '<YourTableName>'
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

Post #783205
Posted Friday, October 30, 2009 7:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 15, 2012 6:28 AM
Points: 2, Visits: 20
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
Post #811943
Posted Thursday, June 10, 2010 4:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 9, 2011 8:17 AM
Points: 7, Visits: 47
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.

Programmers Unlimited
Post #935770
Posted Thursday, July 15, 2010 11:45 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 15, 2014 11:31 AM
Points: 10, Visits: 316
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.
Post #953360
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse