INSERT Statement
INSERT statement is used to add new rows to tables or views. You can insert data by explicitly specifying the values to insert or by retrieving data from other tables or views.
SYNTAX I:
INSERT INTO Table1
VALUES (Value1, Value2, Value3 …)
EXAMPLE:
INSERT INTO Employee
VALUES (1, 'Chan', 'Kevin', 'Product Manager', 1)
Keyword INTO is optional in all INSERT statements. This syntax is generally used when adding values to all the columns in a table. If the number of columns in the table does not match with the number of listed values, an error will be thrown. It is important to note that values need to be listed in the same order as the columns in the table. To avoid the confusion, it is recommended to use the below syntax which also displays the columns which will be affected with the INSERT statement.
SYNTAX II:
INSERT INTO Table1 (Column1, Column2, Column3 …)
VALUES (Value1, Value2, Value3 …)
EXAMPLE:
INSERT INTO Employee (EmployeeID, LastName, FirstName, Title, LocationID)
VALUES (2, 'Mortiz', 'Susan', 'Solutions Arthitect', 1)
Here the column names ”Column1, Column2, Column3 …” are the columns in the table “Table1” into which data needs to be inserted. This syntax is generally used when we don’t want to insert data into all the columns in a table. If a table contains many columns, for debugging and readability purposes, it is better to use this syntax which lists all the columns and the values compared to the previous syntax. It is important to note that when inserting data to specific columns in a table, the columns not listed in the INSERT statement must allow NULL values.
SYNTAX III:
INSERT INTO Table1 (Column1, Column2, Column3 …)
SELECT Column1, Column2, Column3 …
FROM Table2
WHERE [condition]
EXAMPLE:
INSERT INTO Employee (EmployeeID, LastName, FirstName, Title, LocationID)
SELECT 3, 'Miller', 'Martha', 'Systems Arthitect', LocationID
FROM Location
WHERE LocationName = 'San Francisco'
Instead of explicitly specifying the values in the INSERT statement, we can instead retrieve data from another table using the above syntax. You can also create more complicated INSERT statements by combining data from multiple tables or views in the SELECT statement using JOINs.
Insert multiple record sets
Before SQL Server 2008, there were two basic ways to insert multiple record sets in to a table.
SYNTAX I:
INSERT INTO Table1 (Column1, Column2)
SELECT Value1, Value2
UNION ALL
SELECT Value3, Value4
UNION ALL
SELECT Value5, Value6
EXAMPLE:
INSERT INTO Employee (EmployeeID, LastName, FirstName, Title, LocationID)
SELECT 4, 'Edward', 'William', 'Principal QA Engineer', 2
UNION ALL
SELECT 5, 'Mistri', 'Neha', 'Senior QA Analyst', 2
UNION ALL
SELECT 6, 'Wilson', 'Tod', 'Test QA Analyst', 2
SYNTAX II:
INSERT INTO Table1 (Column1, Column2) VALUES (Value1, Value2)
INSERT INTO Table1 (Column1, Column2) VALUES (Value3, Value4)
INSERT INTO Table1 (Column1, Column2) VALUES (Value5, Value6)
EXAMPLE:
INSERT INTO Employee (EmployeeID, LastName, FirstName, Title, LocationID)
VALUES (7, 'Ride', 'George', 'Principal Automation Specialist', 1)
INSERT INTO Employee (EmployeeID, LastName, FirstName, Title, LocationID)
VALUES (8, 'Glenn', 'Trueman', 'Senior Automation Specialist', 1)
INSERT INTO Employee (EmployeeID, LastName, FirstName, Title, LocationID)
VALUES (9, 'Wilson', 'Tod', 'Junior Automation Specialist', 1)
With SQL Server 2008, functionality of the above syntax has been expanded to allow inserting multiple sets of data with just one INSERT statement.
SYNTAX III:
INSERT INTO Table1 (Column1, Column2)
VALUES (Value1, Value2)
,(Value3, Value4)
,(Value5, Value6)
EXAMPLE:
INSERT INTO Employee (EmployeeID, LastName, FirstName, Title, LocationID)
VALUES (10, 'Holder', 'Jack', 'Principal Software Engineer', 1)
,(11, 'Johnson', 'Kevin', 'Senior Software Engineer', 1)
,(12, 'Lam', 'Edward', 'Software Engineer', 1)
Insert values into an Identity column
Identity columns are columns in which SQL SERVER automatically inserts the value when a new row is created. So when inserting values in a table, which contains an identity column, we need to make sure that the identity column name is not included in the INSERT statement. If we want to insert a specific value in the identity column which is different than the next sequential value generated by the SQL SERVER, then IDENTITY_INSERT property for the table needs to be enabled.
SYNTAX:
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1 (IdentityColumn, Column2)
SELECT Value1, Value2
SET IDENTITY_INSERT Table1 OFF
EXAMPLE:
SET IDENTITY_INSERT Location ON
 
INSERT INTO Location (LocationID, Code, Name)
SELECT 1, 'L001', 'San Francisco'
 
SET IDENTITY_INSERT Location OFF