Script a database for specific DML records only

Sometimes the best test data is the data in a live, production SQL Server database. Since using production data is most often not an acceptable option, this requires retrieving the data from a live database table and inserting it into a new table. The challenge arises if there is no need to insert all records from the original table, but only the records that comply with certain conditions.

How to create the Insert into script that moves only specific data from one table to another.

Use an additional table

This solution uses an additional database table where all the records that comply with the criteria are moved first, and then the table data is scripted:

  1. Create a new table where the filtered records will be saved. The table schema has to be exactly the same as the schema of the original table where the records are saved:

    CREATE TABLE Person.PersonPhoneTemp(
                 BusinessEntityID int NOT NULL PRIMARY KEY,
                 PhoneNumber dbo.Phone NOT NULL,
                 PhoneNumberTypeID int NOT NULL,
                 ModifiedDate datetime NOT NULL)
  2. Run the Insert into command that uses Select, to copy data from the original table into the new one.

    In this example, the Person.PersonPhone table has almost 20,000 records. The goal is to script only the records for the Work phone numbers, i.e. where PhoneNumberTypeID = 3

    Run the following SQL to insert these records into the PersonPhoneTemp table:

    INSERT INTO Person.PersonPhoneTemp
           , PhoneNumber
           , PhoneNumberTypeID
           , ModifiedDate
      FROM AdventureWorks2012.Person.PersonPhone

    All 736 records are inserted into the Person.PersonPhoneTemp table

  3. To create a DML script for the Person.PersonPhoneTemp table, start SQL Server Management Studio
  4. Right-click the database in Object Explorer, select Tasks, and then Generate Scripts
  5. In the Choose Objects tab, select the Select specific database objects option and select only the table where the specific records were inserted
  6. In the Set Scripting Options tab, select to save the script to a file
  7. In the Advanced Scripting Options dialog, set to script only data

The generated script contains the Insert into statements for all 736 records where PhoneNumberTypeID = 3:

INSERT INTO Person.PersonPhoneTemp (
            BusinessEntityID, PhoneNumber, PhoneNumberTypeID, ModifiedDate)
VALUES (2, N'819-555-0175', 3, CAST(0x000091BD00000000 AS datetime));

To use this script to insert records into a specific table with a different name, the table name needs to be manually changed in the script.

If there is time and this needs to be implemented only for several database tables, this is a nice solution. However, if there is a busy environment and there is no time to move data from one table to another, script and insert, a better solution is needed. Another disadvantage of this solution is that it can’t be automated and scheduled.

ApexSQL Script is a SQL Server database migration tool which scripts database objects and data into a single or multiple deployment SQL scripts, C# solutions, or executable installers. It can automate and schedule the migration process to run unattended

To achieve the above mentioned process with ApexSQL Script, follow these steps:

  1. Start ApexSQL Script
  2. Select a SQL Server and click the Connect button:

    Connect to a SQL Server

  3. Select a database in the list and click the Load button:

    Select a SQL database from a list of a connected SQL Server

  4. In the Home tab of the main application window, click the Data button to change to Data view grid:

    Data view in the main application window

  5. Select the table where the records that need to be scripted are stored:

    Data grid with tables and columns in the main application window

  6. To specify the criteria for the records, click the ellipse button under the Where column for a selected table
  7. Enter the Where clause condition. Note that the initial Row count is 19,972:

    Setting up the condition to limit the row count in the Where clause dialog

  8. Click the Apply button. The Row count changes to 736:

    Applying the condition and getting the new row count in the Where clause dialog

  9. To avoid manual update of the table name, in the generated DML script, specify the Alias:

    Setting up the Alias for selected table

  10. If there is a need to limit the number of records that fulfill the specified criteria that will be scripted, enter the number of the records that will be scripted in the Max field, which is 700 in this example. To script all the records, leave this field blank
  11. On the Home tab, click the Script button:

    Starting the Script wizard from the Home tab of the main application window

  12. In the first step of the Script wizard, select Data as the Scripting mode and SQL script as the output type in the Output type step, which follows:

    Scripting mode selection in the Script wizard

    Output type selection in the Script wizard

  13. Click the Next button and it will lead to the Dependencies step in which dependent objects are included by default if there are any:

    Dependencies step of the Script wizard

  14. In the SQL script options step, choose the file path and file name, or use default ones:

    SQL script options step of the Script wizard

The created SQL file contains Insert into statements for 700 work phone records as the Max value was set to 700:

INSERT INTO Person.WorkPhone (
            BusinessEntityID, PhoneNumber, PhoneNumberTypeID, ModifiedDate)
VALUES (2, N'819-555-0175', 3, '20020224 00:00:00.000');

Scripting specific data and moving it from one table to another can be easy and automated. Instead of hustling with additional tables where a subset of records is moved to be scripted, use ApexSQL Script to create a DML script directly from the original table that contains all the data, even the records that doesn’t need to be scripted.


April 5, 2013