SQL Server - tool to create insert sql for table data

Asked By John A Grandy

03-Oct-08 05:39 PM

I need a tool to create insert sql for all data in a table ...

So, this tool would generate one insert statement for each row in the table.

Does such a beast exist ?

03-Oct-08 05:43 PM

I have used a few, but they usually turn into paid products.  I liked the
one at http://www.sqlscripter.com but it became rather expensive for the
level of polish.  Also, there is one at http://www.codeplex.com/sqlgen which
is free but does have a few bugs....

03-Oct-08 05:48 PM

Another free one is at http://vyaskn.tripod.com/code.htm#inserts

Tom

03-Oct-08 05:48 PM

Here is a method using stored procedure:
http://vyaskn.tripod.com/code.htm#inserts

--
Plamen Ratchev
http://www.SQLStudio.com

03-Oct-08 07:48 PM

I'm using SQL Scripter ...

On problem I am coming across is how to renumber identity column values.

Easy for a solitary table ... just remove the identity col from the select
list and gen the script ...

But what if there's a 2nd table with an FK join to the first table ...

03-Oct-08 08:17 PM

I am not sure the tool was intended to do that.  It attempts to generate the
scripts for the tables you have chosen verbatim.

03-Oct-08 09:37 PM

Sure , I don't expect the tool to do this for me ...

But this is a tricky problem , because with huge datasets , if you extract
sub-portions of data ( for example a single user's data ) and seed it into a
new db , your identity columns have all sorts of gaps ...

03-Oct-08 09:46 PM

I am not sure what to say.  Can you tell us why you need to renumber the
identity column?

mouser

05-Oct-08 06:24 AM

Stepping back a little, why do you need individual inserts for each
row?  Are you attempting to duplicate the table and all it's data?  Is
it within the same database, or a connected database?  Do you need to
script the creating of the tables, relationships, and the data for an
install?

Look into the tools from Red-Gate Software.  http://www.red-gate.com/
Their SQL Packager might be just what your looking for.  All the tools
have a free trial period.

-Eric Isaacs

05-Oct-08 10:45 AM

And, If you have the management studio (SSMS) of SQL Server 2008, go to
move the data.

--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com

19-Dec-08 06:41 AM

Try http://www.sqlscripter.com to generate data scripts. Insert, Update,
Delete + mix are supported.

12-Apr-11 04:39 PM

Let us suggest Optillect Data Compare SQL. It has special option "Full data transfer mode", which allows to generate all INSERTs for all or particular tables or/and views in your database. Moreover, you can exclude columns and rows you need!

Check it out at

http://optillect.com/products/datacomparesql/overview.html