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.