Create an Insert Statement for Table Records

Question

Post reply

Create an Insert Statement for Table Records

naylor67

Old Hand

Points: 369
More actions
February 17, 2004 at 6:08 am

#161628

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

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

David Burrows SSC Guru Points: 65142 More actions · Answer 2

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.

naylor67 Old Hand Points: 369 More actions · Answer 3

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

David Burrows SSC Guru Points: 65142 More actions · Answer 4

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.

HI3 SSC Rookie Points: 27 More actions · Answer 5

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.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 6

One, I have used with some parameters/options at http://www.databasejournal.com/scripts/article.php/1500031

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

naylor67 Old Hand Points: 369 More actions · Answer 8

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

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]