For this demonstration we will use the HR.Employees table that we created in the post on Table Constraints. Here is a sample of that code.

CREATE SCHEMA HR AUTHORIZATION dbo
CREATE TABLE HR.Employees
(EmpID int IDENTITY PRIMARY KEY,
 FirstName varchar(15) NOT NULL,
 LastName varchar(20) NOT NULL,
 JobTitle varchar(10) NOT NULL,
 HireDate date DEFAULT GETDATE(),
 BirthDate date NULL,
 PhoneNumber varchar(15) UNIQUE,
 DeptCode tinyint)
GO

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.

INSERT HR.Employees
VALUES ('John', 'Deardurff', 'MCT', 
	    DEFAULT, NULL, '555-876-5309', 2)

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 side note here, in a previous blog 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 like 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.

INSERT INTO HR.Employees
VALUES ('John', 'Deardurff', 'MCT', 
	    DEFAULT, NULL, '555-876-5309', 2)

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.)

INSERT INTO HR.Employees
VALUES ('Bjorn', 'Wilde', 'Sales', 
	    '20080517', '19800617', '555-876-5311', 3),
	   ('Robert', 'Builder', 'Marketing',
	    '20070308', '19830820', '555-867-5012', 4),
	   ('Jane', 'Jetson', 'Sales',
	    '20020407', '20761111', '555-867-5013', 3)

With this statement, we added three more rows to the table. In addition, instead of using the DEFAULT and NULL keywords we supplied date values for the appropriate fields. At the end of inserting these records, we can run the following SELECT statement to display the records in our table.

SELECT FirstName, LastName, JobTitle, HireDate,
	BirthDate, PhoneNumber, DeptCode
FROM HR.Employees