Simple SQL Server INSERT statement

By: Jeremy Kadlec

Overview

In order to write an INSERT command you need to know the table name, column names and values you want to add to the table.  Let's take a look at a few SQL Server INSERT examples.

Explanation

Let's explain the code below a little to make sure it makes sense throughout the remainder of this tip.  Here are the points of interest:

• On the first line of the code we insert a record into the [dbo].[Customer] table.
  • As a point of clarification "dbo" is the schema, which can be considered a container to organize tables, views, stored procedures, etc. and assign security.
• Next is a list of the column names which will have data added.
  • Notice that we are not including the CustomerID column in the column list.  This is because SQL Server is managing this value for us.
• Finally, are the list of values we want to add to the row based on the order of the column listing.
• In terms of the values, the varchar and datetime columns are in single quotes and int columns are not. 

INSERT INTO [dbo].[Customer]
 ([FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate])
VALUES
 ('Jonah'
 ,'Hook'
 ,'777-777-7777'
 ,'[email protected]'
 ,1
 ,'2011-09-01')
GO

Let's take a look at a second example:

INSERT INTO [dbo].[Customer]
 ([FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority])
VALUES
 ('Ethan'
 ,'Monkey'
 ,'111-111-1111'
 ,'[email protected]'
 ,1)
GO

What is different about this set of logic?  There is only 1 difference.  The CreateDate column was not included in the column list.  Do you have any idea what will happen?  Do you remember the ALTER TABLE command we issued after creating the sample table?  This command will enter the current date and time when the record is added to the table. 

Let's take a look at one final example and then see how we can verify the results.  Do you remember the NULL explanation above?  How does that apply to this example:

INSERT INTO [dbo].[Customer]
 ([FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate])
VALUES
 ('Lindy'
 ,'Landlover'
 ,'222-222-2222'
 ,NULL
 ,1
 ,'2011-09-02')
GO

In this circumstance, we do not have an email address, so NULL is entered because the value is unknown.

With these three examples under our belt, let's verify the results.