Export SQL Server 2005 query result to SQL INSERT statement?

Question

Is there an easy way in SQL Server 2005 to export query results to a SQL INSERT statement? I'm thinking something along the lines of how you can use Database Explorer to script an existing stored procedure to a new query window.

We're looking to move some data from a dev DB to a production one; I know about linked servers but there's no way the DBA blackshirts will let us set that up.

If you can help me out, I'd appreciate it. A definitive, "Nope, you're boned," will suffice if it has to.

Edit: The table I want to insert into is not on the same database server. Without setting up a linked server, I can't do a select into.

Answer 1

You could use SELECT INTO and INSERT INTO SELECT, both methods are shown here

Edit: maybe I misunderstood your question, and you want to generate INSERT scripts? In that case you could use tools like Apex SQL, SQL Delta or SQL Scripter.

Answer 2

Have you looked at moving the data using DTS (Data Transformation Services)? You can use this to export your data to file etc and then import it to your new database.

Answer 3

The BCP utility suggests itself -- dump the data out into a file, then push it back in on the other server.

Answer 4

iandisme,

We are in the same boat. All database changes must be scripted out and then applied to QA and Prod by someone else (segregation of duties.) So we have a bunch of scripts that script out the insert statements or we just hand build them.

Answer 5

Another colleague of mine came up with a fun solution.

1) Copy query results grid to Excel.
2) Add the SQL commands and parentheses/commas/tick marks in columns between values.
3) Copy grid to a text editor.
4) Find/replace the stuff they needed to change/fix, including extra whitespace.
5) Send SQL to DBA's.

And voila! Several hundred rows queryized. It's ugly as sin but it took less time than looking for a 'sane' way to do it. This happens infrequently enough to suffice as a solution. I do appreciate everyone's suggestions, though - and I do intend to explore them further.

Edit: I am still looking for ideas! I like stuff to be cleaner than having Excel drop a steamer into Notepad++.

Answer 6

If a script file is the only way to add data to your production server, it's easy enough to generate INSERT statements as the result set of a select statement. It's boring and tedious, though, so if you have to do it often, I'd agree with the others who recomend third party tools. If it's just a one-off, though, something like this should work.

select 'insert into MyTable (IntegerField, TextField)
values (' + convert(nvarchar(50),IntegerField) + ', ''' + TextField + ''');'
from MyTable

In SSMS, set "results to text" instead of "results to grid", and you'll get output that's easy to save in a text file and run as a script elsewhere; this, for example:

insert into MyTable (IntegerField, TextField)
values (1, 'a TextField value');
insert into MyTable (IntegerField, TextField)
values (23, 'another TextField value');

It's probably not very performant for large data sets, but given your restrictions it might be the easiest and simplest option.