27
Learn SQL: Microsoft SQL Server - Episode 13: Insert new records in a Table
In this episode we will be discussing a key concept of SQL and database administration, we will be inserting a new record into a table.
We will be using the AdventureWorks database as usual, here we will be inserting a record into the HumanResourcesDepartment table.
We first need to see which columns are available in this table. Inside the HumanResourcesDepartment table there are four columns, namely the DepartmentID, Name, GroupName and ModifiedDate columns respectively.
These are the columns we will be inserting records, we will be performing these tasks using a SQL statement.
In most cases, the primary key (PK) column does not require an explicit value to be entered. This value is automatically generated from the system.
To determine whether we need to add a value to a specific column or not, follow these steps in SMSS.
Right-click on the table-name and click on design. When we click on design we will see all the column names, their data types and whether they allow NULL values or not.
Below we will see a Column Properties window. When we click on our different columns above, we see the column properties below change.
We click on the DepartmentID column; next in the Column Properties window scroll down until we see Identity Specification. We expand this by clicking on the arrow on the left-side.
These values indicate that this column is in fact an identity column. Identity Increment means the identity column increases by 1 every-time a new value is entered and the Identity Seed means the starting value was 1.
In short, every-time we add a new record; we will automatically generate a new value, which will be one higher than the previous highest value.
We could check the other columns to determine if they are Identity columns, however they are not number values and additionally they do not increment, therefore it is safe to assume that DepartmentID column is the only column that is identity in this table.
However we should double-check them to make absolutely certain.
The structure of an insert statement is a bit different from what we have seen thus far.
insert into [Table-Name] (column-name/s)
values (values to insert into sql)For example, to insert a new record in the HumanResourcesDepartment table we can write it as follows.
insert into [HumanResources].[Department] (Name, GroupName, ModifiedDate)
values ('Learning', 'Growth and Education', getdate())Notice how the values in parenthesis, (column-names/s) and (values to insert into sql) need to correspond.
This means Name corresponds to 'Learning'; GroupName corresponds to 'Growth and Education' and ModifiedDate corresponds to getdate().
Getdate is a special built-in function provided by SMSS and allows us to get the current date.
Let’s see this in action within SMSS.
Once we execute this SQL query we get the message 1 row affected followed by the time that the query executed.
We can perform a SELECT statement to confirm our record has been inserted into the HumanResourcesDepartment table.
There you have it, this is how we insert a new record into a table.
Learn continually - there's always "one more thing" to learn.
-Steve Jobs
27