Insert data
Transact-SQL provides multiple ways to insert rows into a table.
The INSERT statement
The INSERT statement is used to add one or more rows to a table. There are several forms of the statement.
The basic syntax of a simple INSERT statement is shown below:
INSERT [INTO] <Table> [(column_list)]
VALUES ([ColumnName or an expression or DEFAULT or NULL],…n)
With this form of the INSERT statement, called INSERT VALUES, you can specify the columns that will have values placed in them and the order in which the data will be presented for each row inserted into the table. The column_list is optional but recommended. Without the column_list, the INSERT statement will expect a value for every column in the table in the order in which the columns were defined. You can also provide the values for those columns as a comma-separated list.
When listing values, the keyword DEFAULT means a predefined value, that was specified when the table was created, will be used. There are three ways a default can be determined:
- If a column has been defined to have an automatically generated value, that value will be used. Autogenerated values will be discussed later in this module.
- When a table is created, a default value can be supplied for a column, and that value will be used if DEFAULT is specified.
- If a column has been defined to allow NULL values, and the column isn't an autogenerated column and doesn't have a default defined, NULL will be inserted as a DEFAULT.
The details of table creation are beyond the scope of this module. However, it is often useful to see what columns are in a table. The easiest way is to just execute a SELECT statement on the table without returning any rows. By using a WHERE condition that can never be TRUE, no rows can be returned.
SELECT * FROM Sales.Promotion
WHERE 1 = 0;
This statement will show you all the columns and their names, but won't show the data types or any properties, such as whether NULLs are allowed, or if there is a default values specified. An example of the output from the query might look like this:
PromotionName
StartDate
ProductModelID
Discount
Notes
To insert data into this table, you can use the INSERT statement as shown here.
INSERT INTO Sales.Promotion (PromotionName,StartDate,ProductModelID,Discount,Notes)
VALUES
('Clearance Sale', '01/01/2021', 23, 0.1, '10% discount');
For this example above, the column list can be omitted, because we're supplying a value for every column in the correct order:
INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.1, '10% discount');
Suppose that the table is defined such that a default value of the current date is applied to the StartDate column, and the Notes column allows NULL values. You can indicate that you want to use these values explicitly, like this:
INSERT INTO Sales.Promotion
VALUES
('Pull your socks up', DEFAULT, 24, 0.25, NULL);
Alternatively, you can omit values in the INSERT statement, in which case the default value will be used if defined, and if there is no default value but the column allows NULLs, then a NULL will be inserted. If you’re not supplying values for all columns, you must have a column list indicated which column values you're supplying.
INSERT INTO Sales.Promotion (PromotionName, ProductModelID, Discount)
VALUES
('Caps Locked', 2, 0.2);
In addition to inserting a single row at a time, the INSERT VALUES statement can be used to insert multiple rows by providing multiple comma-separated sets of values. The sets of values are also separated by commas, like this:
(col1_val,col2_val,col3_val),
(col1_val,col2_val,col3_val)
This list of values is known as a table value constructor. Here’s an example of inserting two more rows into our table with a table value constructor:
INSERT INTO Sales.Promotion
VALUES
('The gloves are off!', DEFAULT, 3, 0.25, NULL),
('The gloves are off!', DEFAULT, 4, 0.25, NULL);
INSERT ... SELECT
In addition to specifying a literal set of values in an INSERT statement, T-SQL also supports using the results of other operations to provide values for INSERT. You can use the results of a SELECT statement or the output of a stored procedure to supply the values for the INSERT statement.
To use the INSERT with a nested SELECT, build a SELECT statement to replace the VALUES clause. With this form, called INSERT SELECT, you can insert the set of rows returned by a SELECT query into a destination table. The use of INSERT SELECT presents the same considerations as INSERT VALUES:
- You may optionally specify a column list following the table name.
- You must provide column values or DEFAULT, or NULL, for each column.
The following syntax illustrates the use of INSERT SELECT:
INSERT [INTO] <table or view> [(column_list)]
SELECT <column_list> FROM <table_list>...;
Note
Result sets from stored procedures (or even dynamic batches) may also be used as input to an INSERT statement. This form of INSERT, called INSERT EXEC, is conceptually similar to INSERT SELECT and will present the same considerations. However, stored procedures can return multiple result sets, so extra care is needed.
The following example inserts multiple rows for a new promotion named Get Framed by retrieving the model ID and model name from the Production.ProductModel, table for every model that contains "frame" in its name.
INSERT INTO Sales.Promotion (PromotionName, ProductModelID, Discount, Notes)
SELECT DISTINCT 'Get Framed', m.ProductModelID, 0.1, '10% off ' + m.Name
FROM Production.ProductModel AS m
WHERE m.Name LIKE '%frame%';
Unlike a subquery, the nested SELECT used with an INSERT isn't enclosed in parentheses.
SELECT ... INTO
Another option for inserting rows, which is similar to INSERT SELECT, is the SELECT INTO statement. The biggest difference between INSERT SELECT and SELECT INTO is that SELECT INTO cannot be used to insert rows into an existing table, because it always creates a new table that is based on the result of the SELECT. Each column in the new table will have the same name, data type, and nullability as the corresponding column (or expression) in the SELECT list.
To use SELECT INTO, add INTO <new_table_name> in the SELECT clause of the query, just before the FROM clause. Here’s an example that extracts data from the Sales.SalesOrderHeader table into a new table named Sales.Invoice..
SELECT SalesOrderID, CustomerID, OrderDate, PurchaseOrderNumber, TotalDue
INTO Sales.Invoice
FROM Sales.SalesOrderHeader;
A SELECT INTO will fail if there already is a table with the name specified after INTO. After the table is created, it can be treated like any other table. You can select from it, join it to other tables, or insert more rows into it.