Scripting your data with SSMS

As developers we often need to move data between SQL Server instances. Some of the time database backups are fine for this, because you want to move everything. But on occasion what you need is a SQL script which can insert some rows of data you had on another server for you. And when I find myself needing this feature, I also find myself having to google how to do it. So to help my memory, here's how to generate scripts for your data:

Steps... url copied!

• Fire up SQL Server Management Studio and drill down to your database. You'll be tempted to go down to the table level (because it's table data you want to script) but don't go that far. Stop at the database that contains your table.
• Right-click your chosen database and select "Tasks" and "Generate Scripts" from the context menus:
• Click "Next" through the welcome page of the "Generate Scripts" window if necessary.
• Check the "select specific database objects" radio button, and find the table(s) you're interested in from the display of objects. Select the ones you want to script for: Then click "Next".
• On the options page of the wizard, you can pick whatever output target you want, but you need to click the "Advanced" button as well:
• Once the "Advanced Scripting Options" dialog opens, scroll down the list of settings to find "Types of data to script" at the end of the "General" section. Change this dropdown from "Schema only" to "Data Only", or if you need both "Schema and data": You may have other settings you want to make here - but this is the important one. And then you can click OK.
• And finally click "Next" a couple of times to start the export process. And finally, click "Finish" to close the wizard.

And that will get you a script file which inclues INSERT statements for all the data rows in your chosen table(s). You can then edit that script to be contain just the data you need, or add in other bits and pieces as required.

It's pretty simple. I'm not sure why I always have so much trouble remembering how to do this - other than blundering around trying to work out why I can't find a sensibly named option on the individual tables. But no excuses now...