Scripting table data in SQL Server 2008R2 and above

As you many of the SQL programmers may already know. In SQL Server, it is possible to prepare and save scripts for database or table schemas. In some cases you may also need to script your data stored in the tables. This is a very simple task but it is possible to miss or forget where it is located and how this was achieved.

Here is how it is done:

1. Open SSMS and right click on the database, select Task and Generate Scripts…

2. “Generate and Public Scripts” wizard will be displayed, bypass introduction by clicking on next

3. Assuming that you will be scripting data for only one table in AdventureWorks DB, you will need to select the “Select specific database objects” option. Then expand “Tables” node and check the table you desire. Let us assume it is “HumanResources.Department”.

4. Click next and “Set Scripting Options” will appear. Click on Advanced button and “Advanced Scripting Options” dialog will appear.

5. Scroll down on the grid to property named “Types of Data to Script” and set it as Data Only. Click OK.

6. Enter the filename you wish to save the script to and then click next. Then on the next screen click Finish.

You will get a bunch of INSERT statements saved to a flat sql text file.

.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s