SQL INSERT

SQL INSERT Statement is used to add new data rows to a table.

SQL INSERT Statement

There are two ways to insert data to a table,

1) Inserting The Data Directly To A Table

Syntax for SQL INSERT Statement

INSERT INTO TABLE_NAME 
[ (col1, col2, col3,...colN)] 
VALUES (value1, value2, value3,...valueN); 
col1, col2,...colN are the names of the columns in the table to which you want to insert data.

There is no need to specify columns, if you are inserting values for all columns. But you need to maintain the order of values as same as order of columns in the table.
INSERT INTO TABLE_NAME 
VALUES (value1, value2, value3,...valueN); 

Example

If you want to insert a row to the employee table, the query would be like
INSERT INTO employee (id, name, dept, age, salary, location) VALUES (105, 'Amar', 'Food Technology', 23, 33000, Bangalore);

NOTE:When adding a row, only the characters or date values should be enclosed with single quotes.

If you are inserting data to all the columns, there is no need to specify column names. The above insert query can also be written as
INSERT INTO employee 
VALUES (105, 'Amar', 'Food Technology', 23, 33000, Bangalore);

2) Inserting Data To Table Through Select Statement

Syntax to use Insert Statement with SELECT

INSERT INTO table_name 
[(column1, column2, ... columnN)] 
SELECT column1, column2, ...columnN 
FROM table_name [WHERE condition]; 

Example

To insert a row into the employee table from a temporary table, the sql insert query would be like
INSERT INTO employee (id, name, dept, age, salary,location) SELECT emp_id, emp_name, dept, age, salary, location 
FROM temp_employee;
If you are inserting data to all the columns, the above insert statement can also be written as
INSERT INTO employee 
SELECT * FROM temp_employee; 
NOTE: We have assumed the temp_employee table has columns emp_id, emp_name, dept, age, salary, location in the above given order and the same datatype.

IMPORTANT NOTES:

  1. When adding a new row, you should ensure the data type of the value and the column matches
  2. You follow the integrity constraints, if any, defined for the table.


Prev - SQL HAVING Clause Next - SQL UPDATE Statement

Comments