SQL Insert query

Last updated on May 11th, 2023

Estimated reading time: 3 minutes

SQL Insert Syntax

The SQL Insert query is used to add one or more rows in Database table.

1.Insert new row with column values for selected columns.

Specify both the column names and the values to be inserted:

INSERT INTO table (column1, column2,...)
VALUES
	(value1, value2,...);

Note: The number of columns and values must be same. The sequence of column names and values to be inserted must be same. Before execution of statement database integrity checks are performed (foreign key constraint, Not Null constraint). If any of the integrity check fails, the insert statement execution fails with error.

2. Adding values for all columns.

If you are adding values for all columns, it is not compulsory to specify column names in Insert query.

INSERT INTO table
VALUES (value1, value2, value3, ...);

Note: The sequence of values must match the order of column names in table.

If the sequence of values is different than sequence of columns in table, then it is compulsory to specify column names otherwise incorrect values would be inserted.

Database Insert Query Example

If you are new to SQL refer this article. SQL UPDATE Query – Full Stack Tutorials Hub

Insert one row into Employee Table

insert into Employee(FirstName,LastName,Email,Street,Zip,City)
values('Suhasini','Rane','suhasini.rane@gmail.com','mumbai','400069','Mumbai')

EmployeeId is auto generated column, and it is not part of Insert Query. Value for Employee Id is automatically generated by Database. The output of the query would display number of rows affected. Since 1 row has been added it would display 1 row affected after insert query executed successfully.

If you are new to SQL SELECT Query refer this article. SQL SELECT Statement – Full Stack Tutorials Hub

How to Insert multiple rows into a table.

To insert multiple rows use following syntax

INSERT INTO table
VALUES
	(value1, value2,...),
	(value1, value2,...),
	(value1, value2,...),
	...;

Insert multiple rows into Employee table.

insert into Employee(FirstName,LastName,Email,Street,Zip,City)
values
('suresh','Rane','suresh.rane@gmail.com','mumbai','400069','Mumbai'),
('sunita','Rane','sunita.rane@gmail.com','mumbai','400069','Mumbai'),
('digarmbar','sawant','digarmbar.sawant@gmail.com','mumbai','400069','Mumbai');

output would display (3 row(s) affected).

How to Copy rows from other tables in SQL INSERT Query

You can use INSERT query to copy one or more rows from another table.

INSERT INTO table1 (column1, column2) 
SELECT
	column1,
	column2
FROM
	table2
WHERE
	condition;

In this syntax select column1,column2 is used in place of values. Where clause is optional.

INSERT INTO Employee (FirstName,LastName,Email,Street,Zip,City) 
SELECT
	FirstName,LastName,Email,Street,Zip,City
FROM
	EmployeeMaster