SQL Server Inserting Multiple Rows

Problem Statement

Getting data loaded into your SQL Server tables can come in a variety of methods. There are many mechanisms that SQL Server offers including BULK INSERT, OPENQUERY, loop logic and subquery inserts to consider. Let’s take a look at a few options that can come into play when you need to load multiple rows of data into a SQL Server table.

Bulk Insert

The first option to consider when you need to load a sizable number of rows into your database is the bulk insert command. This command will accept a local file and multiple other parameters to facilitate the ingress of your data into SQL Server. There are quite a lot of Bulk Insert parameters, for brevity we will cover the most common.

This diagram shows the process of the bulk insert command, data is read from the file system via the command issued from SQL Server Management Studio (SSMS) and that data is broken into batches and inserted to the target table.

Example CSV File :

Create table for bulk insert

IF OBJECT_ID(N'tempdb..#Mock_Data') IS NOT NULL DROP TABLE #Mock_Data
CREATE TABLE #Mock_Data
(
   Id VARCHAR(50),
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(50),
   ip_address VARCHAR(50)
)

The general Syntax for a Bulk Insert command is as follows:

BULK INSERT #Mock_Data
FROM 'C:\Load\MOCK_DATA.csv'
WITH
(
   FORMAT = 'CSV',
   FIRSTROW= 2, --SKIP HEADER ROW OF THE FILE.
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
--VIEW THE DATA INSERTED BY THE BULK INSERT COMMAND
SELECT * FROM #Mock_Data 

Its important to note that for bulk insert to work, you must create the table structure first. For simplicity in this example, I have created all the columns as VARCHAR, in my experience it is easier to import all data as VARCHAR into a temporary table and then cast specific table data types in to your target table.

Parameter	Description
FIRSTROW	Determines which row of data within the CSV file the bulk insert command will start from.
BATCHSIZE	Provide the number of rows that is copied into SQL on a per transaction basis. Default size is 1.
ROWTERMINATOR	Determines the end of a row of data. Default value is “\r\n”.
FIELDTERMINATOR	Specifies the delimiter that is used to separate fields within a data file. Default is “\t”.
FORMAT	Specifies the type of file being inserted.
FIELDQUOTE	Specify the character to be used as a quote character. Default is “.

Subquery Insert Alternative to Bulk Insert

Another valid way to get multiple rows populating into your SQL server tables is by using an SQL INSERT. Where you specify a Select query that will be the data that is inserted into your target table.

This approach is slightly different depending upon:

– If you are creating the table for the first time

– If the table you will be inserting into exists already

Creating table for the first time

-- SELECT * FROM SYS.ALL_OBJECTS DYNAMIC MANAGEMENT VIEW
SELECT * 
INTO #Temp_AllObjects
FROM SYS.all_objects

In this example a new temporary table is created with the query results of:

SELECT * FROM SYS.all_objects

SYS.all_Objects is a System generated view in the data dictionary which contains the name and metadata data about every database object in your database.

–VIEW THE RESULTS

SELECT * FROM #Temp_AllObjects

Table already exists

The syntax is slightly different if the table which you’re inserting into already exists, for this example we will create a temp table and run a TSQL query to populate the table.

–CREATE A TEMP TABLE TO HOLD DATABASE OBJECTNAMES
CREATE TABLE #Temp_AllObjectNames
(
ID INT IDENTITY(1,1),
NAME VARCHAR(MAX)
)

–INSERT NAME FIELD FOR ALL ROWS INTO AN EXISTING TABLE.
INSERT INTO #Temp_AllObjectNames
(NAME)
SELECT NAME
FROM SYS.all_objects

Notice in this variation the syntax is INSERT INTO … Select where as previously the INTO was in between the Select and From clause of the statement.

 --VIEW THE RESULTS
SELECT * FROM #Temp_AllObjectNames

Loop Logic

The last method for getting lots of rows into your database tables is by utilizing Loop logic. This can be useful when you have a subset of tables in your database with the same schema that you would like to dump into a single table.

First lets take a look at a basic loop example:

--EXAMPLE OF INFINITE WHILE LOOP
WHILE EXISTS(SELECT * FROM #Temp_AllObjectNames)
BEGIN
	print format(getdate(), 'yyyy-MM-dd HH:mm:ss') 
	WAITFOR DELAY '00:00:01'
END
  

In this example we use the WHILE EXISTS() keyword to initiate a loop statement, inside the while loop we include a query of the previously generated #temp_AllObjectNames table.

Two main points from this code block:

• As long as there are records in the #temp_AllObjectNames table the loop will continue to run
• The statements between BEGIN and END will execute every 1 second printing the current datetime

This same logic can be applied to generate tons of data into your SQL Server tables. To do this we add a few extra lines of code to help facilitate the INSERT and DELETE process.

Using the ID field:

• We can select the first ID in the #TEMP_DATETIME table and set the variable @ID
• Insert a datetime record into #temp_Datetime
• Delete the ID from the #temp_AllObjectsNames table

IF OBJECT_ID(N'tempdb..#TEMP_DATETIME') IS NOT NULL DROP TABLE #TEMP_DATETIME
CREATE TABLE #TEMP_DATETIME
(
ID INT IDENTITY(1,1),
DATETIMESTAMP DATETIME
)

DECLARE @ID INT
WHILE EXISTS(SELECT * FROM #Temp_AllObjectNames)
BEGIN
	SET @ID = (SELECT TOP 1 ID FROM #Temp_AllObjectNames)
	INSERT INTO #TEMP_DATETIME VALUES(format(getdate(), 'yyyy-MM-dd HH:mm:ss') )
	DELETE FROM #Temp_AllObjectNames WHERE ID = @ID
END
   

For this example each record in the #Temp_AllObjectNames will generate a timestamp in the #TEMP_DATETIME table.

Another interesting way to repetitively insert records is by specifying the keyword go and the number of iterations the statement should run for.

--USE GO TO INSERT 100X DATETIME RECORDS
INSERT INTO #TEMP_DATETIME
VALUES(format(getdate(), 'yyyy-MM-dd HH:mm:ss') )
GO 100
 

This example will insert 100 more datetime records into the #TEMP_DATETIME table.

In Summary

In summary for this article, we covered three different strategies for ingesting multiple rows of data into your SQL Server database. These strategies included the bulk insert command, subquery inserts and loop logic. Each of these examples can be more complex than demonstrated but the ideal was to provide a basic implementation and idea of how to get started with each concept.

For a better experience, try out SQL Manager an alternative to SSMS offered by devart.