INSERT Statement in SQL – DML Statements

---

---

Now in this SQL Post we will learn Data Manipulation Language (DML) INSERT Statement.

INSERT Statement in SQL – DML Statements

---

INSERT Statement – The INSERT Statement is used to insert new records in to a table.

If we want to add some data in to table we need to use  INSERT statement query in SQL.

The SQL INSERT Statement Syntax :

INSERT INTO table_name
(Col1, Col2……ColN) VALUES (Val1, Val2……ValN);

Col1, Col2 – Indicate the Columns name of table.
Val1, Val2 – is the value of columns respectively.
table_name – is the name of table which data to be inserted.

Here, Above SQL INSERT Statement add new rows of data in to a table. The number of columns and the number of values assigned to columns must be same.

If we want to add new data for all columns then we can write above  INSERT Statement like :

INSERT INTO table_name VALUES (Val1, Val2, Val3,Val4);
If table have four columns then we can write INSERT Statement as above.

In above SQL INSERT Statement Val1 insert into Col1, Val2 into Col2, Val3 into Col3 and Val4 into Col4.

SQL INSERT Statement Example :

First we create a new database in database server  and create a new table in database. After creating a table in database we apply INSERT query on table to insert data in to a table.

We have already learned CREATE Database and CREATE Table and SELECT Statement in our previous SQL post.

Now, we have created table named “Student” with some columns like ID, Name, City, Email, Mobile.

If we want to add only three columns values for ID, Name and Email in to table then SQL INSERT query would be like:

INSERT INTO (ID, Name, Email) VALUES (1, “MEERA”, “meera@meeraacademy.com”);
The value inserted in columns ID = 1 , Name =MEERA and Email = meeera@meeraacademy.com

The SQL Student Table :

ID	Name	City	Email	Mobile
1	MEERA	NULL	meera@meeraacademy.com	NULL

If we want to insert data to all columns in to Student table, then INSERT query would be like :

INSERT INTO (ID, Name, Email) VALUES (1, “MEERA”, “meera@meeraacademy.com”);
INSERT INTO Student VALUES (2, “Vaidehi”, “Mumbai”, “vaidehi@yahoo.com”,1122334455);

The SQL Student Table :

ID	Name	City	Email	Mobile
1	MEERA	NULL	meera@meeraacademy.com	NULL
2	Vaidehi	Mumbai	Vaidehi@yahoo.com	1122334455

 

The second way to  insert new records in a table using SELECT Statement.

Add new records in a table using SELECT Statement

In this method insert new records from an existing table.

The INSERT Statement Syntax.

INSERT INTO table_name1 (col1, col2, col3, . . . colN)  SELECT col1,col2,col3, . . . colN FROM table_name2

INSERT INTO table_name1 (col1, col2, col3, . . . colN) SELECT col1,col2,col3, . . . colN FROM table_name2 WHERE Condition

table_name1 – is name of table which records to be inserted.
table_name2 – is name of table which records to be selected for insert.

If we want to add records to all columns, then the SQL INSERT query Syntax :

INSERT INTO table_name1  SELECT * FROM table_name

INSERT INTO table_name1  SELECT * FROM table_name2 WHERE Condition

Example of INSERT Records using SELECT Statement

In above example we have a  table Student with two records. Now add this two records in to new table temp_Student using SELECT  Statement.

The SQL INSERT query would be like : 

INSERT INTO temp_Student  SELECT  *  FROM Student

Above INSERT query inserted all records from table Student in to temp_student

The SQL temp_Student Table :

ID	Name	City	Email	Mobile
1	MEERA	NULL	meera@meeraacademy.com	NULL
2	Vaidehi	Mumbai	Vaidehi@yahoo.com	1122334455

 

If we want to add some columns of records in to table  temp_Student from table Student.
Here, we insert only three columns records ID, Name and Email inserted to temp_Student table from Student table.

The SQL INSERT query would be like :

INSERT INTO temp_Student (ID, Name, Email) SELECT ID, Name, Email FROM Student

Above INSERT query inserted only three columns ID, Name and Email records in to table temp_Student from table Student. The rest of all columns values are NULL.

The SQL temp_Student Table :

ID	Name	City	Email	Mobile
1	MEERA	NULL	meera@meeraacademy.com	NULL
2	Vaidehi	NULL	Vaidehi@yahoo.com	NULL

Here, we can use WHERE Condition in above example.

INSERT INTO temp_Student  SELECT  *  FROM Student WHERE ID=2

The SQL temp_Student Table :

ID	Name	City	Email	Mobile
2	Vaidehi	Mumbai	Vaidehi@yahoo.com	1122334455

Note : In above INSERT query it is not necessary to columns name same for both tables.