#insert into select sql server

New Post has been published on http://www.softwaretestingclass.com/sql-insert-into/

SQL INSERT INTO

In today’s article we will learn SQL INSERT INTO command & how to insert into data from one table to other table. Along with that we will also cover different ways to Insert Data into Specified Columns Only.

What is SQL INSERT INTO?

The primary objective of database systems is basically used to store the data in to database & recall the same state of data from database whenever required. The data is handled by the application which is executed over the top of database. So basically the INSERT INTO command is used to store the data into database. The SQL INSERT INTO command is always add a new record into the table.

SQL INSERT INTO Syntax

Let’s see the basic syntax of Insert into command:

INSERT INTO Table_Name VALUES (Value1, Value2, Value3,...);

You can also use the following command to insert data in specific rows which you have want to specify:

INSERT INTO Table_Name (Column1, Column2, Column3,...) VALUES (Value1, Value2, Value3,...);

INSERT INTO Table_Name – The INSERT INTO command is used to tell database to insert new specified values in the Table_Name which you have specified.

(Column1, Column2, Column3,…) – These are the name of the columns for which you have to add the new record in the table.

VALUES (Value1, Value2, Value3,…) – These are the actual data to be added in the new row.

Before inserting new records, the main important thing to keep in mind is the Data Types. There are different data types & when adding a new row, you should ensure the datatype of the value and the column matches. For all string data types values should be enclose in the single quotes & all numberic data types values should be enter directly without enclosing into single quotes.

Demo Database:

In this tutorial we will use the demo well-known Northwind sample database.

Employee_Id Full_Name Address_Line_1 City Phone

1

Tanya Smith Rua do Mercado Stavanger 9123456789

2

Janet Tampi Keskuskatu Road Chicago 9876543210

3

Nilsen Phil Skagen, 21 Floor NULL 9112233445

4

Nilsen Jones 305 – 14th Ave. S. Suite 3B Atlantic 9988776655

5

Peter Willams Torikatu 38 Sandnes 9944552299

Let’s take a example of Employee table & insert few entries in different fashion using sql server INSERT INTO command. We will add new Employee record in the Employee table with including the column names.

Insert into  Employee (Full_Name, Address_Line_1, City, Phone) Values (‘Wilman Kala’, ‘305 - 14th Ave. S. Suite 3B’, ‘Seattle’, 9988665522);

In this table the Phone value is added as numeric data type, so we have entered Phone value without single quotes.

Same above query can be written as below with excluding the column names:

Insert into  Employee Values (‘Wilman Kala’, ‘305 - 14th Ave. S. Suite 3B’, ‘Seattle’, 9988665522);

Did you observe that we did not insert any value into the Employee_Id field? The Employee_Id column is an AutoNumber field and it is auto increment once the record is added into the table. This AutoNumber is dafult started with 1 and incremented by 1.

Insert Data into Specified Columns Only

While inserting new record if you want to specific values in the records then you should only specify the column name for which you are insert a value & actual value to be inserted. Let’s take a example to insert new record with specified column in query below:

Insert into  Employee (Full_Name, Address_Line_1, Phone) Values (‘Wilman Kala’, ‘Plot no 87,88’, 9988665522);

In above query we have skipped the value of ‘City’ while inserting the new record. Following is the result once we insert the record with specified column values.

SELECT * FROM Employee

Employee_Id Full_Name Address_Line_1 City Phone

1

Tanya Smith Rua do Mercado Stavanger 9123456789

2

Janet Tampi Keskuskatu Road Chicago 9876543210

3

Nilsen Phil Skagen, 21 Floor NULL 9112233445

4

Nilsen Jones 305 – 14th Ave. S. Suite 3B Atlantic 9988776655

5

Peter Willams Torikatu 38 Sandnes 9944552299

6

Wilman Kala Plot no 87,88 9988665522

Inserting into - Insert table data from one table to another

SQL INSERT INTO SELECT command is very much powerful while inserting data from one table to another. Let’s see the basic syntax of the command:

INSERT INTO table1 SELECT * FROM table2;

Let’s take a example where the categories are present in the table. Every month new categories are created & old categories are marked as archived. So in this case we can use this INSERT INTO command to copy all the records from “categories” table to “categories_archive” table using following command:

INSERT INTO ‘categories_archive’ SELECT * FROM ‘categories’;

Once we execute the above script the all categories table values are copied to categories_archive table.

If you want to copy specific values from the categories table then you can specify the specific values in the query for which you want to copy the data.

The query shown below demonstrates its usage.

INSERT INTO `categories_archive`(category_id,category_name,remarks) SELECT category_id,category_name,remarks FROM `categories`;

Executing the SELECT query

SELECT * FROM categories_archive

category_id category_name remarks

1

Comedy Movies with humor

2

Action

3

Thriller

4

Romantic Love stories

5

Science Fiction

6

Cartoons

AnimatedMovies

 Conclusion:

The SQL INSERT INTO statement is used to insert new records in a table.

Single quotes should be excluded while inserting the numeric values.

The string and date values should be enclosed in single quotes.

The INSERT command can also be used to insert data into specified columns only.