What is Insert data

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:

SQLCopy

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.

SQLCopy

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.

SQLCopy

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:

SQLCopy

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:

SQLCopy

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.

SQLCopy

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:

SQLCopy

(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:

SQLCopy

INSERT INTO Sales.Promotion
VALUES
('The gloves are off!', DEFAULT, 3, 0.25, NULL),
('The gloves are off!', DEFAULT, 4, 0.25, NULL);