Script a database for specific DML records only (4210951)

Sometimes the best test data is the data you have in your 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 you don’t want to insert all records from the original table but only the records that comply with certain conditions.

How to create 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 an 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, you have to manually change the table name in the script

If you have time on your hands and you’re implementing this only for several database tables, this is a nice solution. However, if you’re in a busy environment and don’t have time to move data from one table to another, script and insert, you’ll need a better solution. 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, .NET solutions, or executable installers. It can automate and schedule the migration process to run unattended

1. Start ApexSQL Script

2. Select a server and click the Connect button

   

3. Select a database in the list and click the Open button

   
4. In the Main grid, select the Data tab

5. Select the table where the records you want to script are stored

   
6. To specify the criteria for the records, click the Where field

7. Enter the Where condition. Note that the Row count is 19,972

   

8. Click the Refresh 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 you want to limit the number of records that fulfill the specified criteria that will be scripted, enter the number of the records you want to script in the Max field. 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 a scripting mode and T-SQL as an output type

    
13. Click the Next button

14. In the Output file options tab, select the Create and write to file option as an output type and the file path

    

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 you don’t want to script.