For this demonstration we will use the HR.Employees table that we created in the post on Table Constraints. We will begin with just a simple INSERT statement that will add one row into the table. First you may notice that we are not including a value for the EmpID field. This is because it is an IDENTITY column which will automatically supply a value into that field.
We then supplied character data into the FirstName, LastName, and JobTitle fields. Since the HireDate field was supplied with a DEFAULT constraint we just need to use the DEFAULT keyword that will supply the current date into that field. The BirthDate field allows for a NULL value and for now we will use the NULL statement for that field. Finally, we insert the values for the phone number and department code fields.
A keynote here, when we were creating constraints on the HR.Employees table we added a FOREIGN KEY constraint on the DeptCode field that enforced referential integrity with the HR.Departments table. If we tried to INSERT a value into the DeptCode field that did not have a matching value in the DeptID field of the HR.Departments table you would get an error message similar to the following.
Another way of writing the INSERT statement is to include the optional INTO statement. You can also optionally include the column name list as well.
Prior to SQL Server 2008 we would have needed to write a separate INSERT statement for every single record we wanted to add. However, now we can construct several rows all in a single INSERT statement. (For the purpose of this example we will go back to not using the INTO or column name list with our INSERT statement.)
With this statement, we added three more rows to the table. In addition, instead of using the DEFAULT and NULL keywords we supplied date vales for the appropriate fields. At the end of inserting these records, our table should look like the following example.