Sample table for SQL Server INSERT examples

By: Jeremy Kadlec

Overview

For the sake of this tip, let's use this sample customers table as shown below.  Be sure to create this table in your development environment in order to complete the remaining portions of this tip.

Explanation

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

• The "CREATE TABLE" command specifies the schema name i.e. "dbo" and the table name i.e. "Customer".
• The six lines of code between the open parenthesis i.e. "("and close parenthesis i.e. ")" are the columns with the associated data type and NULL condition.
  • For the CustomerID column, "IDENTITY(1,1)" is specified.  This means that as each row is inserted into the table, SQL Server will automatically increment this value by 1 starting with the number 1.
    • This is a point of interest because as we begin to write our INSERT statements, we will need to make sure we do not include this column because SQL Server will manage it for us.
  • There are three data types in this table to include:
    • INT - The abbreviation for integer, which are whole numbers.
    • VARCHAR - Variable character length, which are letters and numbers.  The number in parenthesis after "VARCHAR" is the length of the column.
    • DATETIME - Date and time value
  • In terms of the explanation for NULL, NULL is an agreed upon value meaning "unknown" and there are basically two options when specifying the NULL condition for a column.  The first option is NULL and the second option is NOT NULL.  NULL means that the column can accept a NULL value.  NOT NULL means that the column cannot accept NULL or in other words you need the data for the column.
    • For five of the six columns in this sample table "NOT NULL" is specified.  This means that a value must be included in the INSERT statement.
    • For one of the six columns i.e. "EmailAddress" a value does not need to be entered and NULL will be inserted for that column in that row.
• The "GO" statements are executing the previous set of code as a single batch.
• The "ALTER TABLE" statement in the example adds a default value of "GETDATE()" i.e. the current date for the "CreateDate" column.

Here is the code to create in your development environment to learn about the SQL Server INSERT statement:

CREATE TABLE dbo.Customer
(CustomerID INT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(25) NOT NULL,
LastName VARCHAR(25) NOT NULL,
PhoneNumber VARCHAR(15) NOT NULL,
EmailAddress VARCHAR(25) NULL,
Priority INT NOT NULL,
CreateDate DATETIME NOT NULL)ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_CreateDate] 
DEFAULT (getdate()) FOR [CreateDate]
GO

With this explanation out of the way, let's jump into writing your first INSERT statement.