Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Sample table for SQL Server INSERT examples



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.


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
PhoneNumber VARCHAR(15) NOT NULL,
EmailAddress VARCHAR(25) NULL,
Priority INT NOT NULL,

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

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

Last Update: 9/15/2011

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools