Using SELECT INTO and INSERT to insert SQL rows
Part two of a four-part series of blogs

There are various ways to insert data into new or existing tables in T-SQL - this blog gives examples of each.

  1. Inserting Data into Tables using SQL
  2. Inserting Single Records into a Table (this blog)
  3. Inserting Multiple Rows of Data into a Table
  4. SELECT INTO - Making a New Table from Existing Rows

This blog is part of a full online tutorial in SQL.  Please have a look too at our introductory and advanced SQL training courses for businesses like yours.

Posted by Andy Brown on 12 October 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Inserting Single Records into a Table

If you want to add one row into a table, the syntax is:

INSERT INTO TableName ( column 1, ... column n) VALUES (1, ... n)

As with everything this is best shown by example!

Adding a Single Row into a Table

Suppose that we've created a table of authors:

CREATE TABLE tblAuthor (


FirstName varchar(100),


-- make sure last name is filled in

LastName varchar(100) NOT NULL,

DateAdded datetime DEFAULT GetDate()


For the above table:

  • the value for the AuthorId field will be set automatically
  • The LastName field can not be null, so we must set this
  • The other two fields can be null (if left null, the DateAdded field will take a default value)

All of the following will add a new author into the table.  First, the minimalist approach:

INSERT INTO tblAuthor (





Next, the opposite extreme, giving every field a value:

-- specify values for all fields

INSERT INTO tblAuthor (









A halfway house is as follows (this also shows that you don't need to specifiy the fields in any particular order):

-- fields can be in any order

INSERT INTO tblAuthor (







The result of running the 3 commands above would be the following table:

Three authors in table

The FirstName column has not been specified for the first and third row.


Setting Identity Field (AutoNumber Field) Values

In all of the above examples, the AuthorId was set automatically.  However, you can tell SQL that you will set a value for Identity columns:

-- allow setting value for primary key


-- add author at position number 6

INSERT INTO tblAuthor (









-- resume normal automatic ascending values


-- add another row

INSERT INTO tblAuthor (





select * from tblauthor

The above lines will suspend automatic autonumbering of the AuthorId field for one line (while Jane Austen is inserted as AuthorId 6), then resume automatic numbering again to get:

Five authors with various numbering

Jane Austen is number 6; Robinson is automatically given the next number, 7.


SQL will never allow duplicate values for a primary key, so it's easy to generate errors if you try to take control of an Identity field.

Picking Up on the Identity Field Value Created

When you're writing an application, it is vital to be able to find out the record number of the row just inserted.  You can do this using the following:

What to use What it does
@@Identity Gives the last id field value created using your connection (even if this was created indirectly by a trigger or function).
SCOPE_IDENTITY() Returns the last id field value explicitly created by your connection (this is likely to be the most useful value).
IDENT_CURRENT('tablename') Returns the last id field value created for a specific named table by any connection.

To put this into English: use SCOPE_IDENTITY() to give you the last identity field value created by you, or IDENT_CURRENT('tblAuthor') (for example) to give the last identity field value created for the table tblAuthor.

Here's an example of the use of SCOPE_IDENTITY():

-- create a variable to remember new id number

DECLARE @id int


-- add a record

INSERT INTO tblAuthor (








-- get and store value of identity field



-- show this worked

PRINT 'Just added row ' + CAST(@id as varchar(10))


This is what the above SQL will show when you run it:

Just added message

We see the number of the row just added.


And that completes this discussion about how to insert single rows; what we'll now do is look at adding multiple rows into a table in one go.

This blog has 0 threads Add post