Creating insert statements for posts (DDL - DML)

Question

Post reply

Creating insert statements for posts (DDL - DML)

RD-201664

Ten Centuries

Points: 1334
More actions
October 15, 2007 at 5:37 pm

#180458

Responses to posts often ask the poster to "post DDL". I know how to use Query Analyzer to "script object to new windows as Create, Insert, etc". Is there a way to script the actual data from a table into an insert statement?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

You'd have to write a query to do that. Something like:

select 'INSERT INTO tablename values (', columnname1, ',' ,

columnname2 ')'

from tablename

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 2

there's an excellent stored proc called sp_generate_inserts Written byNarayana Vyas Kondreddi

http://vyaskn.tripod.com

he's got one for 2000 and 2005 and it generated statements like this:

exec sp_generate_inserts tbstate

INSERT INTO [tbstate] ([STATETBLKEY],[INDEXTBLKEY],[STATECODE],[STATENAME],[FIPS])VALUES(1,7002,'AK','Alaska',NULL)

INSERT INTO [tbstate] ([STATETBLKEY],[INDEXTBLKEY],[STATECODE],[STATENAME],[FIPS])VALUES(2,7002,'AL','Alabama',NULL)

INSERT INTO [tbstate] ([STATETBLKEY],[INDEXTBLKEY],[STATECODE],[STATENAME],[FIPS])VALUES(3,7002,'AR','Arkansas',NULL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

You can also open Enterprise Manager, expand the console root to your database, then expand the objects in your database. If you expand the tables, for example, then right click on the table you want to script you will see 'all tasks', then under all tasks, you will see 'gererate SQL script' where you can script your table with the options you want.

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.