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.

Comments

Leave a Comment

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way. Follow @JohnPaulCook