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 numeric 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.

SQL INSERT INTO SELECT command also used to insert one table data from another table.


Related posts:

SQL UPDATE and DELETE
SQL Subquery
SQL Order by Clause – ASC/DESC keywords
SQL SELECT Query
SQL Where Clause
SQL JOIN in Database Testing
SQL UNION
SQL GROUP BY Clause
SQL Functions
Simple Guide to Install MySQL Database And Workbench