Tuesday, November 22, 2011

SQL 2005 - Bulk insert from Text File and Global Temp Tables

1. Introduction

In this article, we will look at some more techniques of inserting data. First, we will explore the usage of the "Temp tables". Then we will look at the "bulk insert using the text files".

2. The Temp Table

As the name suggests the table is temporary and it will get deleted after the usage. SQL developer uses a temp table to store some temporary result in it and query them later. Say for example, if your computation involves data from 6 or 7 tables, apart from other techniques Temp table also one nice technique to make calculations and store it temporarily for later use in that session.

To create the temp table, use the following syntax Example:
Select <Column_list> into #<Temp_Table_name> From <OriginalTableName>

In the above syntax:
Column_List: The list of the column from the main table. You can use comma separated column names or * for all columns
#<Temp_Table_name>: Name of the temporary table. Ex: #MyTemp
 <OriginalTableName>: Name of the original table from which the data is retrieved

Below is the example screen shot for the usage of the temp table. The NorthWnd database is used in two different query window.

Query Session 1

Fig 1. Temp Table at session 1



Query Session 2

Fig 2. Temp table at session 2


We have two queries windows shown in screen shots Fig1 and Fig2. In one query window we are filtering the employees from Seattle and in another window we are filtering the employees from London. The information retrieved is stored in the #EmpTemp temp table. The table with the # sign is known as "Temporary Table". In our example, two temporary table with name #EmpTemp is created for the query session 1 and 2. The two query sessions are shown in the above screenshots with red and green boxes.

The temp table will get deleted automatically when the Query window is closed. That means, the scope of the temporary table is until the end of connected session. And note that each query window maintains a separate connection to the database. This is why you get an error (Stating temp table already exists) when you try to execute the query shown above again in the same query window. 

Now If you look, I used same #EmpTemp in the second query window (Marked as green). Does it effects 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 that you can also use create table query to create the temporary table and then insert the rows inside it.

3. Global Temp Table

Like temp table, you can create the "Global Temp Table" using the ## token before the table name. Right. What is global temp table? The global table is one, which is visible to all the active sessions. Let us say there are 12 users (Or connection. Remember it includes client code connecting to the database) when the global table is created.  Now, this global temp table is available for all 12 users or connected sessions. Now consider the situation below that will make you understand this:

1) All 12 Users 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 available as the user B is active. Note that this user established a session when the global table is 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.
The global temporary table is deleted and it is no longer available.

Below is the usage of the Global temp table and note the ##sign before the table name.

Fig 3. Global Temp Table Example


The QLQuery4 window is making a connection to SQL Server and the “into ##” statement is creating the global temporary table. Now, I opened one more Query window QLQuery5 and this will create one more connection to the SQL server. Then the global table created is accessed in this new session:

Fig 4. Accessing Global Temp table from a dif


It proves that the Global 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 without any problem. Also, note that the Global Table also temporary and it does not belong to any schema.  You can access this table when you are in NorthWnd or Pubs or master. It does not matter.

Now close all the SQL Window (I am assuming that you do not have any client application/network users still maintains a connection to the server). Execute the query shown in the QLQuery5 by opening a new session. What happens? Object does not exist, right?


  1. Once you close the entire query window, the global temp table ##EmpTemp is deleted (In our case)
  2. When you opened a new query window, a new session is created and the Global table becomes no more for it.

4. Bulk insert from text file


As the title suggested we are going to see how do we insert a bulk number of records from a text file to an SQL Server table. The technique is; we should have the records in the text file in such a way that columns and rows of 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.


1) Connect to the NorthWnd database. (You can download the T-SQL for it from SQL 2005 Page of this web-site)
2) Then Query the product table as shown below:

Fig 5. Prepare Text file content for Bulk Insert


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

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

6) Create a table as shown below in the NorthWnd DB

Create table BLKInsertTest(ProdId int, Prodname varchar(40), UnitMeasure varchar(21));

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

Fig 6. Bulk insert from text file - Example

Here,

1: Specifies the name of the file from which we are going to pull the data for the table BLKInsertTest.
2: Field terminator character that tells where each column value ends in the text file for each row.
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.

See U in the Next article.

1 comment:

  1. Wonderful, what a blog it is! This webpage provides useful data to us, keep it up.

    ReplyDelete

Leave your comment(s) here.

Like this site? Tell it to your Firend :)