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.
- 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:
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
- To create a DML script for the Person.PersonPhoneTemp table, start SQL Server Management Studio
- Right-click the database in Object Explorer, select Tasks, and then Generate Scripts
- In the Choose Objects tab, select the Select specific database objects option and select only the table where the specific records were inserted
- In the Set Scripting Options tab, select to save the script to a file
- In the Advanced Scripting Options dialog, set to script only data
CREATE TABLE Person.PersonPhoneTemp( BusinessEntityID int NOT NULL PRIMARY KEY, PhoneNumber dbo.Phone NOT NULL, PhoneNumberTypeID int NOT NULL, ModifiedDate datetime NOT NULL)
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
- Start ApexSQL Script
Select a server and click the Connect button
Select a database in the list and click the Open button
- In the Main grid, select the Data tab
Select the table where the records you want to script are stored
- To specify the criteria for the records, click the Where field
Enter the Where condition. Note that the Row count is 19,972
Click the Refresh button. The Row count changes to 736
To avoid manual update of the table name, in the generated DML script, specify the Alias
- 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
On the Home tab, click the Script button
In the first step of the Script wizard, select Data as a scripting mode and T-SQL as an output type
- Click the Next button
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.
April 5, 2013