SQL Server, generate insert statements

Most developers don’t realize there is an easy way in MS SQL Server to create INSERT statements from a currently populated table. This can be useful for “promoting” data from a development environment to a production development.


This is a quick how-to tip.


To generate a CREATE TABLE statement and INSERT statements for each row in a table, in  SQL Server Management Studio (SSMS)…
  • Right  click on the database (not the table), select Tasks / Generate Scripts… (generate scripts is near the middle of the list). The “Choose Objects” window will appear
  • Check the “Select specific database objects” radio button, expand Tables, and select the specific table, click Next. The “Set Scripting Options” window will appear
  • Use the default selections, but click “Save to new query window” at the bottom. Here’s the tricky part! I’m not sure why Microsoft did this, but click the “ADVANCED” button. At the very bottom, just above the bold “Table/View Options” is the option: “Types of data to script”. Change the corresponding value from “Schema Only” to “Schema and data”. Click OK.
  • Click Next. The “Summary” window will appear.
  • Expand the “Selected Objects” tree value. Review the summary, click Next. The “Save or Publish” window will appear. Review any errors generated. Click “Finish” and the “Generate and Publish Scripts” set of windows will close. You should have a new SSMS tab with the generated script.
Discovering a method to convert from data in a table to a set of INSERT statements can take hours to figure out. It took me hours of digging. Here’s to saving your time!



Keywords: CyberSecurityRecap DBArecap microsoft sql server management studio create insert statements from table data query data creating insert statements table to inserts MS SQL Server management studio

Troy Frericks.
blog 30-Nov-2019
troyf<at>CyberSecurityRecap.com
=
#





Written by Troy Frericks

Leave a Comment

Your email address will not be published. Required fields are marked *