Insert statement results

Question

Post reply

Insert statement results

smknox

Hall of Fame

Points: 3275
More actions
February 13, 2012 at 5:38 pm

#264506

I have been looking for many hours for this result, so apologize if it's already out there and I missed it.
I would like to generate a script to insert records into a test table.
I came across an SP that did just that, but the output is in rows in a query. How do I get that output into a script?
What I want is:
INSERT INTO TableA (colA, colB, colC)
VALUES(1,1,1), (2,2,2),(3,2,1),(1,2,3) etc.
I have the (1,1,1)
(2,2,2)
(3,2,1)
(1,2,3)
nicely in rows in SSMS query panel.
Doesn't seem like this should be hard.

Viewing 6 posts - 1 through 5 (of 5 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could try Mladen Prajdic's ssmstoolspack[/url].

It has a "script results" feature.

-- Gianluca Sartori

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 3

Or, to keep it simple,

INSERT INTO TableA

EXECUTE spName

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

nmederich Old Hand Points: 347 More actions · Answer 4

i would use Excel to generate insert scripts like so: Copy your (1,1,1)

(2,2,2)

(3,2,1)

(1,2,3)

from your query window, open excel highlight cell A1 and paste, highlight cell B2

and input the following formula

="insert into tablea (cola,colb,colc) values " &A1

copy and paste this formula down column B for as many rows as you have. Then copy the formula results from column B into your query window.

It doesn't allow multiple rows of data to be specified in a single DML statement as you show in your example but it gets the job done of getting the data into the test table with minimum of fuss.

smknox Hall of Fame Points: 3275 More actions · Answer 5

greatly appreciate the help. I ended up using a cursor and generating my list fine now.

Thanks,

Sam