Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Simple SQL Server INSERT statement



By:

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.


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.



    



Friday, April 01, 2016 - 4:43:58 AM - san Back To Top

THANKS ~ very clear and detailed explanation ..


Sunday, January 24, 2016 - 4:21:10 PM - Jeremy Kadlec Back To Top

Joe,

Thank you for the feedback and points.  We will get this tutorial updated.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, January 22, 2016 - 5:03:13 PM - Joe Celko Back To Top

 >> Notice that we are not including the CustomerID column in the column list.  This is because SQL Server is managing this value for us. <<

Not quite. IDENTITY is not a column because it does not model an attribute. It is a table property, which counts the physical insertion attempts (not successes) on one disk to one table in one SQL Server instance. It is left-over from the old Sybase/UNIX days of SQL Server used to mimic non-RDBMS tape files.

 

 

You need to explain what happens with IDENTITY if you insert a set of new rows. Set-oriented programming does not match well with traditional sequential data models. 

 


Learn more about SQL Server tools