Looking for tools to export a set of specific rows of data, creating insert scripts

Question

I'm looking for a good tool to script specific data from my database.

In phpmyadmin I was able to write a query and just hit the "export" button to generate insert scripts. I have not yet found anything for MS SQL server that will do this.

It would be great if the tool also offered me the option to do identity inserts or even do delete before insert, check if the record exists and update it, and so on.

Update: Well, little response here, must be a really hard question. I have since found SQL Scripter, it looks adequate. Also seems a pretty amateur-ish project, there must be something more professional out there?

Answer 1

Red-Gate tools can do something like this. They have a data diff tool called SQL Data Compare that will compare two databases, and tell you which rows are different. You could do what you want by scripting an empty version of your database, then have the Red-Gate tool generate what it regards as data synchronization scripts. Those will be packed with INSERT() statements. These tools are not free, but they are worth their weight in gold if you manage lots of databases.

Red-Gate Software

SQL Data Compare

Answer 2

Thanks! I am however looking for something a little simpler, I don't want to create empty copies for all of my databases (and yes, I do manage a lot of them!).

Also, I really need to be able to query just a subset of the data instead of having insert scripts for the whole table.

Answer 3

Microsoft provides a free tool Database publishing wizard, don't know why it is not included in the MS-SQL installation by default.

Answer 4

Drejc it's included for me... However this still doesn't allow me to export a subset of the table contents.