THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

image

image

image

image

image

image

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

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP 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. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John 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. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement