This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Inserting Data into Tables using SQL Inserting Single Records into a Table (this blog) Inserting Multiple Rows of Data into a Table 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.

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.

Inserting Data into Tables using SQL
Inserting Single Records into a Table (this blog)
Inserting Multiple Rows of Data into a Table
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

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 (

AuthorId int IDENTITY(1,1) PRIMARY KEY,

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 (

LastName

) VALUES (

'Smith'

)

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

-- specify values for all fields

INSERT INTO tblAuthor (

FirstName,

LastName,

DateAdded

) VALUES (

'John',

'Wyndham',

'01/01/2010'

)

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 (

DateAdded,

LastName

) VALUES (

'05/01/2011',

'Jones'

)

The result of running the 3 commands above would be the following 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

SET IDENTITY_INSERT tblAuthor ON

-- add author at position number 6

INSERT INTO tblAuthor (

AuthorId,

FirstName,

LastName

) VALUES (

'Jane',

'Austen'

)

-- resume normal automatic ascending values

SET IDENTITY_INSERT tblAuthor OFF

-- add another row

INSERT INTO tblAuthor (

LastName

) VALUES (

'Robinson'

)

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:

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 (

FirstName,

LastName

) VALUES (

'Charles',

'Dickens'

)

-- get and store value of identity field

SET @id = SCOPE_IDENTITY()

-- show this worked

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

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

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.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Inserting Single Records into a Table

Adding a Single Row into a Table

Setting Identity Field (AutoNumber Field) Values

Picking Up on the Identity Field Value Created

Head office

London

Manchester