Export SQL Server 2005 query result to SQL INSERT statement?

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

In that case you could use tools like Apex SQL, SQL Delta or SQL Scripter.

I looked up SQL Scripter... not a big fan of trial software, but the graphical interface might suit some of my more commandline-averse colleagues. +1 for the suggestion – iandisme Jun 28 '10 at 18:24.

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

Looking into the BCP utility now. Looks promising... +1 – iandisme Jun 28 '10 at 18:23 Server won't grant access to the BCP util run locally... but it's still good to know about. Thanks!

– iandisme Jun 28 '10 at 18:42 That sounds like it's just a permissions issue? – Philip Kelley Jun 28 '10 at 18:43 1 "Just a permissions issue" is a death sentence here. Even if I could get permissions, it could take weeks.

– iandisme Jun 28 '10 at 18:57 Bummer. I hate when that happens. – Philip Kelley Jun 28 '10 at 18:59.

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++.

You can use this to export your data to file etc and then import it to your new database.

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.

Yeah we've got that ISO "segregation of duties" dogma here. I really don't want production access ("Here, you hold this grenade. "), but I have a sneaking suspicion that no one really looks at the scripts we send up.

– iandisme Jun 28 '10 at 19:01.

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.

That's pretty similar to what we ended up doing, except with Excel it's a little more scalable (easy enough to copy the same stuff to several thousand rows) without being all scary like using grep and regex. – iandisme Jun 29 '10 at 16:13.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.