THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SSMS Tips and Tricks: Scripting Inserts

Sometimes you need to be able to script the loading of a table. SSIS and the Import/Export Wizard can do this, but there are times when you really need a complete set of insert statements. Red Gate’s SQL Data Compare or SQL Packager can do this, but not everybody has the budget for a commercial tool. SQL Server Management Studio 2008 can script the insert statements necessary to populate a table. I learned this from my coworker Oleg Netchaev, who learned this from Chris Howarth, who read it in Paul Randall’s blog. This trick is important enough to publish it again and with additional details.

The feature is part of the SSMS Generate Scripts wizard, not the database. That means you can use SSMS 2008 to script the inserts for a table in an earlier version of SQL Server. Although the objective is to only script a table, you start the Generate Scripts wizard at the database level. Select the database of interest and right-click to select Generate Scripts.







In case you are wondering what I did with the table I scripted, it was used for a join in a query on a production database. I needed to run a query against live production data and restrict the results to match conditions in a development table. Linked servers were out because of security rules. Nothing could be modified in the production database. I globally edited the inserts from the Generate Scripts wizard changing them to insert into a temporary table on the production database. The production tables were joined to the temporary table. The query was run without changing or violating any production server security standards.

Published Tuesday, October 13, 2009 10:03 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement