I do a lot of development work and I'm always generating Insert statements for test and development use. I also find I have a recurring need to move data from one database to another and I always seem to be firing up a database synch tool.
I use a variety of diff and snych tools (Apex SQL and RedGate make nice stuff), but at times I wish I had a quick little menu option to script out table data without having to go through the whole process of starting up an external program and walking through a series of wizard steps.
I recalled seeing a SSMS add-in on Code Project that let you right-click on a table and it would generate the insert statements needed, but when I went looking for this, I was unable to find anything except invalid links and page not found errors. Since I already had template code from writing other add-ins, my solution was to take a couple hours and roll my own. (And now that I have done so I'm sure someone will send me a link to another add-in that does the same thing....)
If you have similar data needs then you can download it from here.
The operation of the utility is fairly straightforward.
- After installing, when you right-click on a table node in Management Studio, you will see a "Script Data for [table name] option in the menu.
- Selecting this will bring up a dialog letting you choose a few file options (File, Clipboard, New Query Window) and a few script options. (Disable Constraints, Triggers, etc...)
Since this is just a basic utility, I did not add too many options. It's easy enough to add more options but I did not need them for my requirements so for now, you're stuck with the following...
- All data in the table gets scripted, with the exception of blob fields, which will be replaced with NULL. (This is by design as converting this data to text generates horrendously large files, causing Management Studio to choke on loading.)
- Identity Insert will be enabled if the table has an identity field.
- You cannot chose individual columns to script.
- You cannot chose sub-sets of data (where clause).
If you find yourself needing more options that I have supplied (which is likely), then I suggest checking out one of the stand-alone programs from ApexSQL or RedGate, as they are full of options.
This add-in works best for tables with small amounts of data. If you have a large amount of data then I recommend you use the "Script to File" option, as you are likely to run out of memory. This will of course, vary from machine to machine.
And really, if you have tons of data, you should probably be using BulkCopy as using multiple insert statements is not terrible efficient.
Feel free to contact me with any bugs or suggestions.