1. About This Bulk Insert Example

In the base article on Insert, we saw how to insert data into a table. In this article, we will look at more techniques of inserting data. These techniques are useful for bulk insert of data. First, we will explore the usage of the ‘Temp tables’. Then we will look at the ‘bulk insert using the text files’.

2. Bulk Insert Into Local Temp Tables ‘#’

2.1 Syntax

As the name points, the table is temporary and it will get wiped out after the usage. SQL developer uses a temp table to store some temporary result in it and query them later before it expires. Say, for example, if a report involves data from 6 to 7 tables, apart from other techniques, Temp Table  also one nice technique to make calculations and store it temporarily for later use.

To create the temp table, one use the following syntax Example:

In the above syntax:

• Column List is the list of the column from the main table. One can use comma separated column names or * for all columns.
• #<Temp_Table_name> is the Name of the temporary table. Ex: #MyTemp
• <OriginalTableName> is the Name of the original table from which the data is retrieved

2.2 Example

Below is the example for the usage of the temp table. We use the NorthWnd database in two different query windows.

Query Session 1

Query Session 2

We have two queries windows shown in screenshots above. In one query window we filter the employees from Seattle and in another window, we filter the employees from London. We store the information retrieved in the #EmpTemp temp table. The table with the # Token is known as “Temporary Table”. In our example, SQL Server creates two temporary tables for the query session 1 & 2 with name #EmpTemp. The two query sessions are shown in the above screenshots with red and green boxes.

SQL Server deletes the temp table when the user closes the Query window. This means the temporary table lives only till the end of the connected session. Also note, each query window holds a separate connection to the database. Therefore, we get an error stating temp table already exists when we try to execute the query shown above again in the same query window.

In the example, we used same #EmpTemp in the second query window (Marked as green). Does it affect the content of the #EmpTemp created in the query window marked in red? No. Because the temp table scope is session based and SQL see both the temporary table as two different instances even though they have the same name.

Note, one can also use create table T-SQL to create the temporary table and then insert the rows inside it.

3. Bulk Insert Into Global Temp Tables ‘##’

Like a temp table, we can create the ‘Global Temp Table’ using the ‘## Token’ before the table name. What is Global Temp Table? It is one which is visible to all the active sessions to the database. Let us say there are 12 users or connection to the database when the global table was created.  Now, this global temp table is available for all 12 users or connected sessions. For a better understanding consider the situations below:

1. All 12 Users are active – The Global table is available for all the 12 users.
2. 3 Users disconnected and a new user, say B is connected to SQL Server – The Global table is still available for remaining 9 Users as well as the newly connected user/Connection B.
3. All users disconnected except B –  The Global table is still alive, and the user B is holding the connection. Note that this user established a session when the global table was alive. That means he is also possibly using it. So, SQL still allows Global Temp Table to be alive.
4. User/Connection B is also terminated and there is no user at present – SQL Server deletes the Global Temp Table and it is no longer available.

Let us Experiment

Below is the usage of the Global Temp Table and note the ‘## Token’ before the table name.

The QLQuery4 window is connecting to SQL Server and the “ into ##” statement is creating the global temporary table. Now, we will open one more Query window QLQuery5 to have one more connection to the SQL server. The below picture shows how the table is available for this second session as well.

It proves that the Global Temp Table is accessible by other connection also. Think like this; the second query window (QLQuery5) is opened on a different machine and the global table is accessible there as well with no problem. Also note, this Global Temp Table is temporary, and it does not belong to any schema.  We can access this table when we are accessing the database NorthWnd or Pubs or master. It does not matter which database we connected to.

Now we close all the SQL Window and assume that we do not have any client application/network users still maintaining a connection to the server. With that assumption, we can now execute the query shown in the QLQuery5 by opening a new session. What happens? SQL Server says, “Object does not exist”, right?

This is what happened,

1. Once we close the entire query window, SQL Server deletes our Global Temp Table ##EmpTemp.
2. When we opened a new query window, we create a new session, and the Global table became no more for it.

4. Bulk Insert Via Text File

As the title suggested, we will see how to insert a bulk number of records from a text file to an SQL Server table. First, we must have the records in the text file in such a way that columns and rows of the records are terminated by unique letters. We call these letters as ‘Field Terminator’ and ‘Row Terminator’. We will experiment this with a quick walk through.

• Connect to the NorthWnd database. (You can download it from Here)
• Then Query the product table as shown below:

• Next, Click on the No Column name to select the entire row under this single column.
• Then, Right click and copy the selected rows and paste it to a notepad.
• Save it as Products~.txt in D:\

The text file now has the data for the bulk insert experiment. Note that the field terminator is a ‘~’ and row terminator is a new line character a ‘\n’. Now we will create a table that will consume the data from this text file.

• Create a table as shown below in the NorthWnd DB.

• Run the below Query to insert the data from the text file to the table created in the previous step

Here,

• Marker 1: Specifies the name of the file from which we are going to pull the data for the table BLKInsertTest .
• Marker 2: Field terminator character that tells where each column value ends in the text file for each row.
• Marker 3: Row Terminator tells what is the row terminator. In our example new line is the row terminator.

Note that executing the query inserts 77 rows at once.

• C# StatusStrip Control Explained
• Using C# SQLCommand and SQLDataReader in ASP.Net Web Pages

Categories: SQL Server

Tags: Bulk Insert, Global Temp Table, Local Temp Table

1 reply ›

1. 

   1111

   March 15, 2022 • 1:34 AM

   The font this website uses gave cancer to cancer.

   Loading...

   Reply ↓

This site uses Akismet to reduce spam. Learn how your comment data is processed.