Create an Insert Statement for Table Records
-
Is there a way to take all the rows in a table and create an insert script for each record? Thanks in advance.
Regards
Bob
-
I am NOT taking credit for this script, SOMEONE ELSE WROTE IT and I believe it may help you:
(It has some LENGTH Limitations but can get you started)
set nocount on
declare @TableName sysname
, @ColumnName sysname
, @ColumnType int
, @ColOrder int
, @TableID int
, @ExecStr varchar(4000)
, @HeadStr varchar(4000)
, @MaxCol int
, @ColList bit
set @TableName = 'Customers'
set @ColList = 1
set @TableID = object_id(@TableName)
set @HeadStr = '('
select @MaxCol = max(colorder)
from syscolumns
where id = @TableID
declare ColumnList cursor scroll for
select name, type, colorder
from syscolumns
where id = @TableID
order by colorder
open ColumnList
fetch first
from ColumnList
into @ColumnName, @ColumnType, @ColOrder
while @@fetch_status <> -1
begin
set @HeadStr = @HeadStr +
case when @ColOrder < @MaxCol then @ColumnName + ', '
else @ColumnName + ')'
end
fetch next
from ColumnList
into @ColumnName, @ColumnType, @ColOrder
end
if @ColList = 1
set @ExecStr = 'SELECT ''INSERT INTO ' + @TableName + ' ' + @HeadStr + ' VALUES ('' + '
else
set @ExecStr = 'SELECT ''INSERT INTO ' + @TableName + ' VALUES ('' + '
fetch first
from ColumnList
into @ColumnName, @ColumnType, @ColOrder
while @@fetch_status <> -1
begin
set @ExecStr = @ExecStr +
case when @ColumnType in (48, 50, 52, 56, 60) then 'convert(varchar, ' + @ColumnName + ')'
when @ColumnType in (61) then ''''''''' + replace(convert(varchar, ' + @ColumnName + ', 102), ''.'', '''') + '''''''''
else '''N'' + '''''''' + ' +'replace(' + @ColumnName + ', '''''''', '''''''''''') + '''''''''
end +
case when @ColOrder < @MaxCol then ', '
else ' + '')'' FROM ' + @TableName
end
fetch next
from ColumnList
into @ColumnName, @ColumnType, @ColOrder
end
deallocate ColumnList
--exec ('select ''dummy'' [INSERT INTO ' + @TableName + ' (], *, ''dummy'' [)] from ' + @TableName + ' where 1 = 2')
select @HeadStr
exec (@Execstr)
HTH
* Noel -
SELECT 'INSERT INTO
VALUES (' +
CAST(intcol as varchar) + ',' +
'''' + varcharcol + ''',' +
'''' + CONVERT(varchar,datetimecol,120) + '''' +
')'
FROM
Or do you want script for any table?
(There maybe one somewhere on this site)
Far away is close at hand in the images of elsewhere.
Anon. -
David:
The whole purpose of this is to take the contents of 4 tables off of a server and drop their contents into an identical table at home for testing. The script that noeld posted works, with some problems on integer fields. The problem is it is not putting a comma before the field in the column. I modified on piece to remove the N from prefixing every column (see modification below). If I can get the comma infront of the integer fields in the table this may do the trick. I am new at script writing for SQL and appreciate all your help.
CODE MODFICATION:
while @@fetch_status <> -1
begin
set @ExecStr = @ExecStr +
case when @ColumnType in (48, 50, 52, 56, 60) then 'convert(varchar, ' + @ColumnName + ')'
when @ColumnType in (61) then ''''''''' + replace(convert(varchar, ' + @ColumnName + ', 102), ''.'', '''') + '''''''''
else ''','' + '''''''' + ' +'replace(' + @ColumnName + ', '''''''', '''''''''''') + '''''''''
end +
case when @ColOrder < @MaxCol then ', '
else ' + '')'' FROM ' + @TableName
end
fetch next
from ColumnList
into @ColumnName, @ColumnType, @ColOrder
end
-
Try changing
case when @ColOrder < @MaxCol then ', '
to
case when @ColOrder < @MaxCol then '+'',''+ '
Far away is close at hand in the images of elsewhere.
Anon. -
You can use this
http://www.sqlservercentral.com/products/dbezze/
It works really good and has a feature that will generate insert statements for every record in table for you. You can provide a query that can narrow down the search results for the records in the table you want to create inserts for.
-
One, I have used with some parameters/options at http://www.databasejournal.com/scripts/article.php/1500031
-
If you only what to find a way to take some table data home, why don't you export those tables to flat files or some other format and import this at home?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
i want to make it easy for my friend to just run the script file and not have to worry about importing a flat file.
-
Ok, what about bcp the data out and create a *.bat file that bcp the data back in?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply