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
   SELECT
          BusinessEntityID
          , PhoneNumber
          , PhoneNumberTypeID
          , ModifiedDate
     FROM AdventureWorks2012.Person.PersonPhone
   WHERE
          PhoneNumberTypeID
          =
          3;

   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:

   

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

   

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

   

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

   

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:

   

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

   

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

   

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:

    

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:

    

    

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:

    

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

    

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.