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.
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.
Determines which row of data within the CSV file the bulk insert command will start from.
Provide the number of rows that is copied into SQL on a per transaction basis. Default size is 1.
Determines the end of a row of data. Default value is “\r\n”.
Specifies the delimiter that is used to separate fields within a data file. Default is “\t”.
Specifies the type of file being inserted.
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),
–INSERT NAME FIELD FOR ALL ROWS INTO AN EXISTING TABLE.
INSERT INTO #Temp_AllObjectNames
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
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 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.