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.
|
1 2 3 4 5 6 7 |
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:
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:
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.
|
1 2 3 4 5 6 7 8 |
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:
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:
|
1 2 3 4 5 |
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:
|
1 2 |
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:
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.
- It first creates the destination table disTypeOneMore.
- 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.
|
1 2 3 4 5 |
Select discounttype , discount into DisTypeOneMore From Discounts; Select * from DisTypeOneMore; |
The Picture shows Data Clone of a Table:
Note: The examples here uses Microsoft supplied Pubs database.
Categories: SQL Server
Tags: SQL Insert Into..Select, SQL Select..Into
Do you like this Example? Please comment about it for others!!
This site uses Akismet to reduce spam. Learn how your comment data is processed.