The INSERT Statement

1. The Basic INSERT Statement

It is the most basic statement to insert data into a table. Its syntax is as follows:

INSERT INTO <table> [(column_list)]
VALUES (data_values)

INSERT is the action statement.

INTO is an optional keyword but it is recommended to use for readability.

column_list is an optional listing of all the columns in the table. If this is missing, the data values must exactly match with the order of columns in the table.

data_values is list of actual values for the related columns. the order of data values must match with column_list or, if it is missing, the structure of the table.

INSERT INTO stores
VALUES ('TEST', 'Test Store',
'1234 Anywhere Street', 'Here', 'NY', '00319')

This example omit the column_list. so, the data_values should match to the column list of the table.

INSERT INTO stores
(stor_id, stor_name, city, state, zip)
VALUES
('TST2', 'Test Store', 'Here', 'NY', '00319')

This example includes the column_list. so, the data_values should match to column_list but not to the column list of the table.

2. INSERT INTO … SELECT Statement

When we need to insert the result from a select query, we can use this statement. The result set created from the SELECT statement becomes the data that is added into the INSERT statement. Its syntax is as follows:

INSERT INTO <table name>
( < column list > )
<SELECT statement>

Example:

INSERT INTO SelOrders (OrderID, CustomerID)
SELECT OrderID, CustomerID FROM Orders

Let’s Master T-SQL

T-SQL Related topics to be covered:

1. Basic SQL Statements

2. Administrative Statements

3. Reporting