How to generate batches of SQL Server INSERT statements with the maximum allowed number of row values

When working with SQL databases, you may find the need to copy data from one table into another table with an identical or similar schema, either within the same database or across different databases. Often in these cases, the volume of data you need to copy will be quite large, and therefore, performance will be a potential concern.

For SQL Server and Azure SQL databases, SQL Server Management Studio provides tools to generate data scripts. However, the functionality it offers can only generate individual INSERT statements for each generated table row, which can be inefficient for large datasets.

INSERT statements with multiple associated row value expressions perform much better, but there are limits to the number of records you can create as part of a single INSERT.

In this article, I will demonstrate how to implement a custom SQL script that generates T-SQL INSERT statements in batches, such that the aforementioned limits are not exceeded.

Tool limitations

Before reviewing the custom script that we’ll create with the more efficient INSERT statements, let’s review why this is necessary due to the current tool limitations.

SSMS

SQL Server Management Studio (SSMS) provides a range of helpful tools to assist with administering SQL Server databases, including the ability to generate scripts.

Let’s walk through how we can use the Generate Scripts feature of SSMS to generate a data script.

I’ll be working with the sample AdventureWorks database that is frequently used for demos, but you can try the feature out on any database you like.

After connecting to your SQL Server instance using SSMS, expand the ‘Databases’ node and right-click on the database you want to work with to reveal the context menu, then click on Tasks –> Generate Scripts.

At this point, you will be presented with the ‘Generate Scripts’ dialog, and you can click on the ‘Next’ button to skip past the Introduction to the Choose Objects step, as shown in the screenshot below.

In the above screenshot, you can see that I have chosen the ‘Select specific database objects’ radio button and I’ve selected the ‘Sales.Customer’ table under the ‘Tables’ node.

Press the ‘Next’ button to proceed to the Set Scripting Options step.

Within the Set Scripting Options step, click on the ‘Advanced’ button and select the ‘Data only’ option from the ‘Types of data to script’ combo-box, then press the ‘OK’ button.

Back on the main dialog, I recommend selecting the ‘Open in new query window’ radio button, as shown in the screenshot above, then press the ‘Next’ button twice and finally the ‘Finish’ button to complete the wizard.

After completing the above steps, SSMS will have generated a data script with contents similar to the following.

USE [AdventureWorks]
GO
SET IDENTITY_INSERT [Sales].[Customer] ON 
GO
INSERT [Sales].[Customer] ([CustomerID], [PersonID], [StoreID], [TerritoryID], [rowguid], [ModifiedDate]) VALUES (1, NULL, 934, 1, N'3f5ae95e-b87d-4aed-95b4-c3797afcb74f', CAST(N'2014-09-12T11:15:07.263' AS DateTime))
GO
INSERT [Sales].[Customer] ([CustomerID], [PersonID], [StoreID], [TerritoryID], [rowguid], [ModifiedDate]) VALUES (2, NULL, 1028, 1, N'e552f657-a9af-4a7d-a645-c429d6e02491', CAST(N'2014-09-12T11:15:07.263' AS DateTime))
GO
INSERT [Sales].[Customer] ([CustomerID], [PersonID], [StoreID], [TerritoryID], [rowguid], [ModifiedDate]) VALUES (3, NULL, 642, 4, N'130774b1-db21-4ef3-98c8-c104bcd6ed6d', CAST(N'2014-09-12T11:15:07.263' AS DateTime))
GO
...

Note that I have included an ellipsis (…) at the end of the content to indicate that there are more lines in the generated SQL text than are shown above, to keep the output short for demonstration purposes.

As you can see above, an INSERT command with a single VALUES counterpart is generated for every record from the ‘Sales.Customer’ table.

This output will work just fine for many scenarios and generally offers acceptable performance when the dataset is around 10,000 records or less (your mileage may vary). However, as the number of records increases, you will likely notice a significant degradation in performance.

The other disadvantage of this approach is the inability to customise the columns that are inserted; notice that even the CustomerID identity value is included. Additionally, it isn’t possible to alter any of the values if needed.

Custom script

Instead of relying on tools such as SSMS, we can write a custom script to select the table data we need.

It’s a bit more involved than using a tool to do the job for us, and we need to write the script carefully to avoid data issues. However, this approach offers us the freedom to include the specific columns we need, and we can customise the columns and associated values where required.

SQL query

The SQL script below is an example of how we can implement such a script for the ‘Sales.Customer’ table from the AdventureWorks database.

WITH RowValues AS (
    SELECT
        '(' +
            --  e.g.  1
            COALESCE(CAST(PersonID AS NVARCHAR(20)), 'NULL') + ', ' +
            --  e.g.  2
            COALESCE(CAST(StoreID AS NVARCHAR(20)), 'NULL') + ', ' +
            --  e.g.  3
            COALESCE(CAST(TerritoryID AS NVARCHAR(20)), 'NULL') + ', ' +
            --  e.g. N'3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'
            'N' + QUOTENAME(CAST(NEWID() AS NVARCHAR(36)), '''') + ', ' +
            --  e.g. CAST(N'2014-09-12T11:15:07.263' AS DATETIME)
            'CAST(N''' + CONVERT(NVARCHAR(27), ModifiedDate, 126) + ''' AS DATETIME)' +
        ')' AS RowText,
        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber,
        COUNT(*) OVER () AS TotalRowCount
    FROM Sales.Customer
)
SELECT
    -- Create records in batches of 1000 row values, to avoid exceeding the maximum number of allowed row value expressions for INSERT statements.
    IIF((RowNumber - 1) % 1000 = 0 OR RowNumber = 1, 'INSERT INTO Sales.Customer (PersonID, StoreID, TerritoryID, rowguid, ModifiedDate)' + CHAR(13) + CHAR(10) + 'VALUES' + CHAR(13) + CHAR(10), '') + 
    RowText + 
    IIF(RowNumber % 1000 = 0 OR RowNumber = TotalRowCount, ';', ',')
FROM RowValues;

The above script consists of two main parts: a Common Table Expression (CTE) that generates the row values text, and a SELECT statement that combines the INSERT text with the VALUES text.

The CTE carefully formats the row text, taking care of nullable columns, such as ‘PersonID’ using the COALESCE function, and using other appropriate functions to convert each column value to a valid string representation, according to its data type. The current row number is calculated using the ROW_NUMBER function and the total row count is calculated via the COUNT function.

The SELECT statement uses the IIF function to conditionally include the INSERT and VALUES text at the start of every 1000 rows of data, followed by the row text that was generated by the CTE, followed by a comma for each row value expression and a semi-colon at the end of every 1000 rows of data.

Query output

Running the query from the previous subsection against the AdventureWorks demo database will produce output that is similar to the following.

Note that you should run the query with the ‘Results to Text’  (Ctrl + T) option toggled on within SSMS to allow the output to be easily copied and pasted into a new query window for execution.

INSERT INTO Sales.Customer (PersonID, StoreID, TerritoryID, rowguid, ModifiedDate)
VALUES
(NULL, 934, 1, N'56338C58-887D-4997-B15A-719F8BA5225F', CAST(N'2014-09-12T11:15:07.263' AS DATETIME)),
(NULL, 1028, 1, N'A9BD85AF-0B26-471C-9F77-3E372068D5E3', CAST(N'2014-09-12T11:15:07.263' AS DATETIME)),
(NULL, 642, 4, N'24DBEFDC-AF1F-4AD7-BA6E-81C08553E391', CAST(N'2014-09-12T11:15:07.263' AS DATETIME)),
...
(13588, NULL, 4, N'46A6EC98-A01A-453E-91F1-0F45253C54C7', CAST(N'2014-09-12T11:15:07.263' AS DATETIME));
INSERT INTO Sales.Customer (PersonID, StoreID, TerritoryID, rowguid, ModifiedDate)
VALUES
(18920, NULL, 4, N'F726C35E-D350-407C-B683-7EDE4EF43529', CAST(N'2014-09-12T11:15:07.263' AS DATETIME)),
(13098, NULL, 6, N'526BB48C-A067-4877-8171-3DF9D558C8EB', CAST(N'2014-09-12T11:15:07.263' AS DATETIME)),
(17545, NULL, 1, N'E2E11512-2D75-4DD6-BA10-5C21C8F789ED', CAST(N'2014-09-12T11:15:07.263' AS DATETIME)),
...
(1993, 1994, 3, N'0C706C4F-A1C4-46EA-9583-97AAE7A89780', CAST(N'2014-09-12T11:15:07.263' AS DATETIME));
...

Note that I have included ellipses (…) to indicate that there are more lines in between the generated SQL text than are shown above, to keep the output short for demonstration purposes.

The batching of the INSERT statements to include 1000 row value expressions at a time is vital. In addition to allowing us to insert large volumes of data efficiently, if we don’t break up the statements in this way, we’ll receive the following error when we try to insert the data.

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

At this point, you can copy and paste the output into a new query window within SSMS and execute the generated query to insert the records. You should find that the performance is much better when dealing with large numbers of records compared to the style of script that SSMS will generate for you.

After reviewing this example, it should now be possible for you to implement a similar version of the script to suit the schema of the table that you want to copy data from.

Additionally, you should consider wrapping the query commands within a SQL transaction to ensure that changes are rolled back if any errors occur when inserting the data.

Summary

In this article, I covered how to implement a custom SQL script that generates efficient INSERT statements for SQL Server database table data.

I started by demonstrating how to generate data scripts using the SSMS tools and highlighted the inherent performance and customisability limitations of the same.

I then showed how you can create a SQL script that generates INSERT statements in batches of 1000 row value expressions at a time to avoid errors related to the maximum allowed number of row values, while also underscoring the customisation options that are available with this approach.