Database Scripting

There are many times where you will need to script out data for tables for use in a deploy or a development environment. SQL Server Management Studio (SSMS) has a scripting tool that is available to all without third party tools. You can script schema and/or data. The downside is that it will generate an insert statement for every row in every table you script. It may not be as efficient has other tools; but, it gets the job done.

Let’s look at how to script out tables and a few potential problems to watch out for. If you have a team that needs data to work with locally to test before checking changes into the project, you can provide a script to run in their database.

The Scripting Task will evaluate dependencies and generate the scripts in the correct order. During testing you may find you have to add a few tables to meet all of your foreign key constraints.

Two issues to be aware of are self-referencing tables and circular references. A self-referencing table example would be Employee who has a ManagerID that is EmployeeID in the Employee table. You will have to set the constraint to NOCHECK at the top of the script and add the CHECK constraint back at the end of the script after it is generated. The circular reference must be removed before you start the scripting task. Otherwise, it will not know which order to script the tables. In my version of SSMS 2012 it crashes SSMS and needs to be restarted. When the developer publishes the database it will have the appropriate foreign keys. Additional constraints may have to be removed prior to inserting data and added at the end of the script to allow for data to be loaded prior to enforcing foreign keys.

Start by right clicking on the database and selecting Tasks/Generate Scripts

 

Choose Select specific database objects and the tables you would like to script.

Click Next and you will be at the Set Scripting Options dialog box.

I prefer to Save to new query window so I can make any adjustments needed.

Then click on Advanced button.

 

You will have to change these two parameters. You can also generate schema; but, in this example we are only scripting for data.

Click Next until the script is generated.

Script sample:

INSERT [dbo].[Customer] ([CustName], [Address1], [Address2], [City], [StateCode], [Zip]) VALUES (N'Customer Name                                     ', N'Address Line 1', N'Address Line 2', N'ALABAMA STATE City', N'AL', N'19001')

GO

INSERT [dbo].[Customer] ([CustName], [Address1], [Address2], [City], [StateCode], [Zip]) VALUES (N'Customer Name                                     ', N'Address Line 1', N'Address Line 2', N'ALASKA STATE City', N'AK', N'19002')

GO

You can now use this script to load test data locally. The script can be checked into source control so it is centralized and available to everyone on the project.