The SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
90 | Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
91 | Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
INSERT INTO Example
The following SQL statement inserts a new record in the "Customers" table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
Try it Yourself »
The selection from the "Customers" table will now look like this:
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
90 | Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
91 | Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
92 | Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
Try it Yourself »
The selection from the "Customers" table will now look like this:
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
90 | Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
91 | Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
92 | Cardinal | null | null | Stavanger | null | Norway |
The INSERT INTO statement is used to insert single or multiple records into a table in the SQL Server database. Here, we will insert data into the following Employee table which we created in the Create Table chapter. The following INSERT INTO statement will insert a single row in all columns of the above Employee table in the SQL Server database. INSERT INTO Employee(FirstName, LastName, EMail, Phone, HireDate, Salary)
VALUES('John','King','[email protected]','123.123.0000','01-01-2015', 33000);
Note that EmployeeId column is an identity column, so the values will be auto-generated on
each insert statement. So, EmployeeId column is not included in the above insert statement. To see the inserted data, execute the Select * from Employee; query in the query editor, as shown below. To insert
values to all columns of a table, you don't need to specify column names with the table name. Specify the values for each column in a sequence as they appear in the table, as shown below. INSERT INTO Employee
VALUES('Neena','Kochhar','[email protected]','123.000.000','05-12-2018',17000);
Any change in the sequence, the number of values, or its data type may result in an error or incorrect data. To insert data into specific columns, specify the column names in the parenthesis. Make sure other columns allow null
values; otherwise, an error will be raise.Insert Data into Tables in SQL Server using INSERT Statement
Syntax:
INSERT INTO table_name(column_name1, column_name2...)
VALUES(column1_value, column2_value...);
Insert Values to All Columns
Insert Values to Specific Columns
The following will insert data in FirstName, and LastName columns only.
INSERT INTO Employee(FirstName, LastName) VALUES('James','Bond');
You must insert data to all NOT NULL columns; otherwise, it will raise an error.
Insert Multiple Records
Insert multiple records in a single INSERT INTO statement by having multiple records in parenthesis after VALUES. The following will insert two records in the Employee table in SQL Server, MySQL, PostgreSQL, SQLite database.
INSERT INTO Employee VALUES ('Kevin','Weiss','[email protected]','123.123.12','08-10-2019',17000), ('Lex','De Haan','[email protected]','123.123.13','05-05-2019',15000), ('Laura','Bissot','[email protected]','123.123.15','02-08-2019',40000);
To insert multiple records into specific columns, specify the column names in the parenthesis, as shown below.
INSERT INTO Employee(FirstName, LastName) VALUES ('Kevin','Weiss'), ('Lex','De Haan'), ('Laura','Bissot');
Now, execute the Select * from Employee query will display the following result.
Want to check how much you know SQL Server?