Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

SQL Insert Data Techniques

1. Introduction To Data Insertion

We all know that to insert data into SQL server database we use the Insert statement. This article points out entering data into the database table using various techniques possible. First, we will start with normal insert statements. Then we will go ahead with inserting data from existing tables in the SQL Server Database. This article requires Microsoft Pubs database.

2.Inserting All Column Values

Look at the below SQL statements. The first one Use Pubs; informs SQL server that it should perform the queries 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;
Go

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

The below picture shows columns definition of Pubs table:

Jobs Table From Pubs Database

Jobs Table From Pubs Database

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

Below is the result of executing the SQL Statement:

Inserting data for all columns in the table

Inserting data for all columns in the table

3. Inserting Data for Specific Columns

Now look at the below insert statement. Here, after the table name discounts, column names separated by commas are specified within the parenthesis. We call this as a Column List. Next to it is the Value List. In the values list, we provide the values in the same order in which we provided column names in the column list. If the value is a text string, a single quote is used to enclose the string.

Use Pubs;
Go

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

Below is the result of executing the Query:

Insert Into Specific Columns

Insert Into Specific 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:

CREATE TABLE DisType
(
      discounttype varchar(40),
      discount decimal(4, 2) NOT NULL
);

We just created a Distype  table with two columns. And we will 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. We also stated the column names from it. SQL Server uses these columns as insertion target. Instead of passing the values manually, we specified a source table to pick the data. As we are looking values for only two columns DiscountType, Discount in the target table DisType, we retrieve those two columns in the source table Discounts through a Select statement. Note, it does not require that column name should match. But we should match the data type for smooth insertion of data from source to destination table. The screenshot below shows new Table with data:

Insert Into with Select

Insert Into with Select

5. Bulk Insert by Creating Destination Table

Consider the below SQL statement. Even though the statement looks like a select statement, 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 disTypeOneMore.

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

Select discounttype , discount
into DisTypeOneMore
From Discounts;

Select * from DisTypeOneMore;

The Picture shows Data Clone of a Table:

Data Cloning of a Table

Data Cloning of a Table

Note: The examples here uses Microsoft supplied Pubs database.

Categories: SQL Server

Tags: ,

Do you like this Example? Share your thoughts!!

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