Monday, August 10, 2009

Scripting data

Scripting database objects, or entire databases, has always been easy in SQL Server, but scripting the data to populate the database tables used to be a different matter. A number of third-party tools were and are available that do the job nicely, but all the most useful ones have to be paid for.

However, I discovered an option for scripting data INSERTs straight from the SQL Server 2008 Management Studio. It's a little hidden away, but it's there and it works beautifully.

Select a database in SSMS, right-click, and choose "Tasks/Generate Scripts..." from the context menu.

Under the "Choose Script Options" wizard page set "Script Data" as True, set the other options as appropriate, select the necessary voilà:

Good or what? It works on SQL 2005 dbs too. It's obviously not the answer to every problem, but it's a nice extra to have.