Ever needed to generate insert statements for the data in a table? This feature is actually included in SQL Server Management Studio (from version 2008), but is somewhat hidden.
This guide will show how to script schema and data to a file.
In the example, I am using the database AdventureWorks2012. The table Production.Product contains 504 rows, so it will be useful for demonstration purposes.
Right click the database and select “Tasks” -> “Generate Scripts”. This will open the “Generate and Publish Scripts” dialog. Select the option to “Select specific database objects” and check the table Production.Product:
Click on “Next” to get to the “Set Scripting Options” dialog. Leave the “Output Type” to the default, and choose “Save to new query window” (This is for demonstration purposes only, use your own preferences in these settings):
Before you click “Next”, we need to change one of the advanced settings. So press the button “Advanced”. This setting controls if the generated script will contain the schema, the data, or both:
Select “Schema and data” and click on “Ok”. Back in the “Set Scripting Options” dialog, click on “Next”. A summary window of your selections will be shown, click “Next” again. A sql file will open in a new window in SSMS. The schema is scripted first, and after that the data is scripted as insert statements.
