My First INSERT Statement
Last week we covered how to get information out of a table, using a SELECT query.
This week, we will discover some of the myriad ways to put data into a table.
The good news is the concept is straightforward: we have a list of columns in a table, and each column has a data type. We will insert a row into the table, according to the column order and data type.
In reality, inserting data into a table is fraught with complexity.
Using our Stores table from before, the simplest way to write an INSERT statement is as follows:
INSERT INTO [dbo].[Stores]
VALUES (NULL,
N'West Edmonton Mall',
N'8882-170 Street, Edmonton, AB, T5T 4M2',
N'Stephanie West', N'stephanie@example.com');
Note: Remember that our store’s name and address, and manager’s name and email address, are all stored as Unicode (NVARCHAR), so the string has to be prefixed with an N. This guarantees that whatever is between the quotation marks is Unicode already and won’t be converted behind the scenes and potentially cause problems.
Ambiguity with column order
Our first problem is that it’s not clear which columns are being inserted into, nor their order.
Without a list of columns in the INSERT statement, the database engine will insert in the order provided, into whatever columns are on the table, and it might even do implicit conversions on data types behind the scenes.
It is therefore good practice to include the column names when writing an INSERT statement, like so:
INSERT INTO [dbo].[Stores] (
[StoreCode],
[StoreName],
[Address],
[ManagerName],
[ManagerEmail]
)
VALUES (
NULL,
N'West Edmonton Mall',
N'8882-170 Street, Edmonton, AB, T5T 4M2',
N'Stephanie West', N'stephanie@example.com'
);
Now we are sure that the column order is correct, and even if the table somehow has a different column order (for instance, the ManagerEmail and ManagerName are swapped around), this statement will succeed.
Where’s the StoreID column?
The astute reader has noticed there is no reference to the StoreID column, which happens to be the Primary Key for this table.
This is one of the ways a lot of accidental DBAs (and even experienced DBAs) get tripped up. If we think back to the table definition, we used an IDENTITY value.
To refresh our memories, an IDENTITY value is an auto-incrementing integer value, generated by the database engine, in order to ensure that the Primary Key is unique.
Note: It is because the IDENTITY is set that this column is being excluded from the INSERT statement above. Primary Keys which don’t have an IDENTITY set must be included in INSERT statements, provided that the value(s) being inserted will be unique.
NULL Values
We spoke about null values in the beginning of the series, and in this example we can see how to insert a NULL value into a table, provided the column definition allows nulls in the first place. We happen to know that the StoreCode is a nullable column.
Default Values
It is possible to exclude columns that have default values on them. If we think about it, an IDENTITY column is a column that has a default value assigned to it, which just happens to be an auto-incrementing integer.
We might want to have columns that have a default value of the current date and time. This convention is used when auditing database events by adding a CreatedDate column (for example) that defaults to the current date and time, using DATETIME2(7), when a row is inserted.
Another example might be to use a default value of 0 in a bit column and update that value later on.
In these cases, columns with default values can be excluded from the INSERT statement, because the database engine will automatically put the default value into that column.
That being said, there’s nothing stopping us from using a different value for a column that has a default value. If we have a default value on a column, like our DATETIME2(7) example, we could override that default value as long as we include the column and a new value in the INSERT statement.
Adding a column to the table
What happens if, during the course of regular business, a column is added to the Stores table? Unless that column has a default value, both examples of the INSERT statement above will fail.
The final results
If you’re following along in SQL Server Management Studio, we can run the INSERT statement like so:
INSERT INTO [dbo].[Stores] (
[StoreCode],
[StoreName],
[Address],
[ManagerName],
[ManagerEmail]
)
VALUES (
NULL,
N'West Edmonton Mall',
N'8882-170 Street, Edmonton, AB, T5T 4M2',
N'Stephanie West', N'stephanie@example.com'
);
Once we have executed the statement (using F5 on the keyboard, or clicking the Execute button in the toolbar), we see a message in the Messages pane:
(1 row(s) affected)
Using our recently-learned skill to SELECT the data from this table, we will see the new result set, containing the new row:
Congratulations! We have put a new row of data into a table, and a SELECT query proved that it was inserted.
Notice that the NULL value has a different background colour to the rest of the data. This is a way to distinguish actual null values from string columns that might just have the word “NULL” stored there.
Next time, we will be updating data in a table using the UPDATE command. Stay tuned.
Look me up on Twitter if you want to ask any questions about this series, on @bornsql.