Basic INSERT statement
Published: Jul 15, 2013
This article is a simple and brief introduction to the INSERT statement within SQL Server. This, as the name suggests, is the method used to enter data into our databases.

For this example we'll use a Person table which you'll need to create using the following script:

if OBJECT_ID('dbo.person') is not null drop table dbo.person
go

create table dbo.person
(
    id smallint identity(1, 1) not null,
    title varchar(4) not null,
    firstName varchar(20) not null,
    lastName varchar(30) not null,
    hairColour varchar(10) null,
    isParent bit not null,
    dateCreated datetime not null default(current_timestamp),
    modifiedDate datetime null,
    constraint pk_person primary key (id)
)


Having created our Person table we now need to fill it with data otherwise it’s pretty useless. To do this we use the INSERT command which, as suggested, is used to insert data into tables.

There are numerous ways in which we can do this, although they all use the same basic command…


insert into <table>(<columns>) values(<data>)


Using this we will insert our first piece of data…

insert into dbo.person
(
  
title, firstName, lastName, hairColour,
  
isParent, dateCreated, modifiedDate
)
values ('Mr', 'Bart', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null)


There are some interesting things to note here… firstly we enter strings (varchar, char etc) data with single quotes around them, this is also true for dates (eg. '2012-09-03'), whereas numerical values do not have such quotes and are written “as is”.

Also a key point to note is that if a column is an “identity” then it will automatically increase and therefore you do not need to list this column or value as this will cause an error. It is possible to directly insert into this column, but that is rarely applicable, much more complicated, and is discussed here.

The next key point to note is that we inserted a null value into the modifiedDate column. If you refer to the table definition, modifiedDate is the only column that can accept nulls and therefore we do not have to populate this column (it can be left blank).

SQL Server understands the concept of “leaving something blank” for columns in which a null is allowed, and therefore you can do exactly that by not listing the column in the insert list, and not entering a value in the value list.

For example let’s add Lisa Simpson to our table, also with a null value for the modifiedDate…

insert into dbo.person
(
  
title, firstName, lastName, hairColour,
  
isParent, dateCreated
)
values ('Ms', 'Lisa', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP)


As you can see, SQL allows this quite happily.

To verify what has happened, type and run the following:

select *
from dbo.person


This is an incredibly basic SELECT statement. This is covered more fully here, but for the moment this will suffice.

You should see the following:

This verifies that in a nullable column you can leave out the column name and the null value. SQL Server will deal with this for you.

The next thing to note is that the dateCreated column has a default value in the table create statement. That value is the current timestamp. In the same manner as the modifiedDate column, we can leave this out of both the column list and the values. If you leave it in either you will receive an error as the number of columns in the column list must match the number of values in your value list.

Let’s insert Marge into our table (also note the change in value for the “isParent” flag)…

insert into dbo.person
(
  
title, firstName, lastName, hairColour,
  
isParent
)
values ('Mrs', 'Marge', 'Simpson', 'Blue', 1)


Again, we have the output we expect as SQL Server has inserted our default value for us along with the null modifiedDate…

Next up we have Homer. For the sake of this article (because I’m going to use this same data in my UPDATE and DELETE guides), I’m going to presume Homer is bald and therefore has no hair colour.

As we can see, hairColour is nullable for this very reason. So now we’re down to an even smaller list of columns. (Also note you can shuffle the column names around as you please, so long as you also move the value order around to match):

insert into dbo.person
(
  
lastName, title, firstName, isParent
)
values ('Simpson', 'Mr', 'Homer', 1)


As you can see, despite much fewer columns, and a different order, the data is still correct and as expected:

Last, but not least, we have Maggie. For this I’ll show one last difference in syntax which is that if you are listing all values and in the correct order, then you do not need to include a column list. This is not advisable for application code as if the table changes (new columns etc) then your code will fail, but for a quick insert or ad hoc query, it can be a time saver:

insert into dbo.person
values ('Ms', 'Maggie', 'Simpson', 'Yellow', 0, CURRENT_TIMESTAMP, null)


Again, the output is as we require:

Multiple Inserts



Obviously you wouldn’t want to insert single rows of data all the time, you may want to load several items at once. For this there are a few different methods. The manual entry methods will be shown below, but if you want to load some data from another table (maybe to make a subset of data) then you can would combine the INSERT with a SELECT statement as follows:

insert into <table> (<columns>)
select <columns>
from <anotherTable>


This will become more apparent once you have learnt more about the SELECT statement, but I have included it here purely for completeness.

The more manual approach to entering multiple items of data are as follows:

insert into dbo.person
values ('Mr', 'Peter', 'Griffin', 'Brown', 1, CURRENT_TIMESTAMP, null),
       (
'Mrs', 'Lois', 'Griffin', 'Red', 1, CURRENT_TIMESTAMP, null),
       (
'Mr', 'Chris', 'Griffin', 'Blonde', 0, CURRENT_TIMESTAMP, null)


Or…

insert into dbo.person
select 'Ms', 'Meg', 'Griffin', 'Brown', 0, CURRENT_TIMESTAMP, null
union all
select 'Mr', 'Stewie', 'Griffin', null, 0, CURRENT_TIMESTAMP, null
union all
select 'Mr', 'Brian', 'Griffin', 'White', 0, CURRENT_TIMESTAMP, null


Using values in this manner is only applicable in SQL 2008 and above, whereas using SELECT and UNION ALL will work in all versions.

This leaves us with a working table upon which we can practice our other basic commands:

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron