August 6, 2011

SQL 2005 - Inserting Table Data Techniques - Bulk Insert

1. Introduction

We all know that to insert data into SQL server database we use the Insert statement. This article explains inserting data into the database using various techniques available. First, we will start with normal insert statements. Then we will go ahead with "Bulk Insertion" and "Temporary Table" usage. OK let us move on.

2. Insert for all the columns

Look at the below SQL statements. The first one Use Pubs; informs SQL server that the queries should be performed on the Pubs database. In the below query we are executing the insert statement on the Jobs table of the Pubs database. All we specified is the table name jobs and some list of values separated by a comma between the open and close parenthesis.

Use Pubs;

--Insert values for all column except the one taken care by SQL2005
insert into jobs(15,'Editor 2',10,100);
Select * from Jobs;

Fig 1. Jobs Table from Pubs database

In the above picture, the table Jobs actually has job_id column and we are not inserting the values in the above query for it. This is because that particular column is an auto populated key column. We will discuss that in some other article. So in the Insert statement if I am not specifying the column names, then I am inserting values for all the columns exist in that table.

Below is the result of executing the SQL Statement:

Fig 2. The inserted column

3. Inserting Data for Specific Columns

Now look at the below insert statement. In the below statement after the table name discounts, column names separated by a comma is specified within the parenthesis. Also in the values list, the values are provided in the same order in which column names are provided in the column list. If the value is a text string, a single quote is used to enclose the string.

Use the Pubs database.

--Insert data to a specific column
insert into discounts(discounttype, discount)
values('Seasonal Discount', 11.15);
Select * from Discounts;

Below is the result of executing the Query:

Fig 3. Data inserted selectively for two columns

4. "Insert into ... Select" technique

Before we go, first we will create a table for inserting the data using "Insert into...Select" technique. Below is the table create statement:

      discounttype varchar(40),
      discount decimal(4, 2) NOT NULL

We just created a Distype table with two columns. And we are going to insert data into this table from the existing Discounts table in the Pubs database.

Now look at the below insert statement:

Insert into DisType(Discounttype,discount)
Select DiscountType, Discount from Discounts;

Here, we specified the target table DisType as the insertion target and also specified the columns in that table for that insertion data being supplied. Instead of passing the values manually we specified a source table to pick data. As we are looking values for only two columns DiscountType, Discount in the target table DisType, those two columns in the source table Discounts is retrieved through select statement. Note that it does not require that column name should match. But the data type should be matched for smooth insertion of data from source to destination table.

5. Bulk insert by creating Destination table

Consider the below SQL statement. Even though the statement looks like a select statement actually it performs two tasks. 

  1. It first creates the destination table disTypeOneMore
  2. It queries the Source table Discounts and inserts the resultant data into the destination table just now created.

The column names and data type of the "Destination Table" is taken from the select statement of the "Source Table" Discounts.

Select discounttype , discount
into DisTypeOneMore
From Discounts;

Select * from DisTypeOneMore;

Note: The tables involved in the examples are part of the pubs database that Microsoft ships as sample db.

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)