Overview: SQL Server 2008 makes it very easy to script out the data from one or more tables. I find this technique to be useful for moving data between environments (ex: from my laptop development environment to a shared development environment), for sharing data with a client or coworker, or for creating data scripts for source control. This technique is helpful for data movement I plan to do only once (i.e., meaning an SSIS package would be a bit of overkill).

The below screen shots are from SQL Server 2008 R2.

Steps to Create Data Scripts

1. In SQL Server Management Studio, right-click the database name, select Tasks, then Generate Scripts.

2. The Introduction screen lists the steps. Next.

3. At the Choose Objects screen, you can choose the entire database, or specific objects (Tables, Users, Schemas). Scripting the entire database is the default.

In this example, I have chosen 9 specific tables. Next.

4. The Scripting Options page lets you choose a file, clipboard, or a new SSMS query window. There’s also a Web service radio button. Save to file is the default. In this example, I have selected a new query window. Don’t click Next yet!

5. Click the Advanced button - here’s where all the good stuff is! In the “Types of data to script” you can choose Schema only (which is the default), Data only, or Schema and data.

There are also a number of other really useful options, such as whether or not to include logins, indexes, foreign keys, statistics, default values, etc. within the script. You can also choose whether or not to script just the CREATE, or a DROP and CREATE.

In this example I chose “Schema and data,” and left all other defaults in place.

6. The Summary screen lists the selections made. Next.

7. The final screen. Choose Save Report if you’d like, then Finish. That’s it! Pretty cool, eh?

If you opted to click the "Save Report” button, the report produced looks like this:

Output from the Script

Above, I requested it script the Schema and Data for me. Therefore, my SSMS window contains (1) CREATE TABLE scripts, and (b) INSERT scripts.

Each row to be inserted is formatted all on one row, which is difficult to read with all the scrolling to the right. For a short script I might take the time to reformat it. Alternatively, you can turn on word wrap (Edit menu > Advanced > Word Wrap).

Finding More Information

MSDN: Documenting and Scripting Databases

MSDN: How to Generate a Script (SQL Server Management Studio)