(Warning: technical post ahead.) Ever since leaving the PowerBuilder/Sybase/ERWin world behind, something I’ve missed is the ability to easily generate portable SQL scripts for populating a table with test data. There are plenty of solutions in SQL Server for migrating data—DTS/Integration Services, BCP, and others. But DTS and Integration Services have to be maintained in the increasingly clumsy SQL utilities and cannot be easily inspected to see if things have changed, and BCP is opaque—you can’t really examine a BCP result file in any easy way to see what the data looks like within. No, give me DML—even if it’s bulky, a long list of INSERT/UPDATE statements has the advantage of being easily readable and even modifiable.
Unfortunately, there isn’t an easy way using the Microsoft tools to produce DML from existing data in a table; all the scripting support in the old SQL Enterprise Manager and the new SQL Server Management Studio are aimed at producing DDL scripts that create or modify the tables. Management Studio in SQL Server 2005 will create template scripts for insert or update scripts, but won’t actually put data into them—a curious omission.
SQL Scripter to the rescue. This nifty app offers the ability to script the data from any or all tables from a database as insert, update, or insert when new/update when existing statements. There’s even features for export of the data to CSV, Excel, and PDF. Pretty cool for a free utility. I’m now changing my process for creating a new demo database to use SQL Scripter to move my demo data from one environment to another.