Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script Table Data to Insert Statements


Script Table Data to Insert Statements

Author
Message
Tatsu
Tatsu
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 307
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
Alexander Zeitler
Alexander Zeitler
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
Hi,

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

Alex



kuldipMCA
kuldipMCA
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 144
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
devavejay-806985
devavejay-806985
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Furby
Furby
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
kermitgreen
kermitgreen
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
LAJ
LAJ
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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' -- ''
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


lifuhai
lifuhai
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
titan2782
titan2782
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
David Masciangelo
David Masciangelo
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 369
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search