Have you ever needed to insert a large number of records into a SQL Server table? In the past, you may have used a cursor or a while loop to accomplish this task. However, there is a more efficient way to achieve the same result.

Today, we will explore a cool feature in SQL Server Management Studio (SSMS) that allows you to specify the number of executions of a batch. By passing a number after the batch separator, you can insert a million records into a table with just a few lines of code.

Let’s take a look at an example:

-- Change database context
USE DatabaseNameHere;

-- Suppress messages
SET NOCOUNT ON;

-- Create test table
IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = 'TopTip')
    CREATE TABLE TopTip (
        TopTipID INT IDENTITY(1, 1),
        Col1 VARCHAR(100),
        Col2 VARCHAR(100),
        Col3 VARCHAR(100),
        Col4 VARCHAR(100)
    );

-- Now this is the cool bit!!!
INSERT INTO TopTip (Col1, Col2, Col3, Col4)
VALUES ('This', 'Is', 'Cool!', 'asaADSFHKUASASDFJ9IEW8R934HR4C398T0WMCRWPEOKCPMO3R')
GO 1000000;

As you can see, by adding “GO 1000000” after the INSERT statement, we are instructing SSMS to execute the batch 1 million times. This means that a million records will be inserted into the “TopTip” table.

It’s important to note that SSMS may highlight the “1000000” as a syntax error, but rest assured, it does work.

So, the next time you need to insert a large number of records into a SQL Server table, give this technique a try. It will save you time and effort compared to using cursors or while loops.

Remember, SQL Server is a powerful tool with many features that can help you optimize your database operations. Stay tuned for more SQL Server tips and tricks!

Enjoy!

Chris