(SQL Server 2005) Good way to manage a large number of insert statements?

Question

I have a stored procedure that takes a table name and writes out a series of INSERT statements, one for each row in the table. It's being used to provide sample, "real world" data for our test environment.

It works well but some of these sample rowsets are 10, 20k records. The stored proc writes them out using the PRINT statement and it's hard to copy that many lines and paste them into the management studio to run them. Is there a SQL redirect feature I might be able to use, perhaps to write this output to a table and a way to loop through and run each statement that way? Just a thought.

I'd like to do this all from within the management studio and not have to write a C# program to create a dataset and loop over it, etc. I'm essentially looking for suggestions on a good approach. Thanks very much.

Answer 1

Use EXEC:

PRINT @INSERT_statement
 EXEC @INSERT_statement

...to run the query.

But I'd recommend looking at bulk insertion to make the data load faster:

• BULK INSERT

Answer 2

Where is your stored procedure getting this data?

You may want to look into importing it as a table and then running your stored procedure against that inserted table. SQL Server Management studio has many options for importing data.

If your stored proc is generating the data - then that's a whole other issue.