Inserting Data into Tables using SQL

The previous part of this tutorial section showed how to create tables using SQL - this one shows how to populate them with data.

When I was first learning SQL, these were some of the commands that I found the hardest to remember.  The problem is that there are 3 ways to do this, and each does a different thing.  The 3 ways are:

The last two methods use similar syntax, and are particularly easy to confuse.

This blog will consider each of the 3 methods shown above in turn!

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

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:

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:

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

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

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:

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.

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:

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():

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.

Instead of inserting rows one at a time, you can take the set of rows returned by a SELECT statement and add these at the bottom of an existing table, using:

The only rule is that the fields you're inserting must be of the same data type as the fields you're inserting into. 

Inserting Multiple Rows - the Vanilla Version

To show how this works, let's set up two similar tables to hold names of authors (notice that the number, order and names of the columns aren't identical).  First a table to hold the source authors:

Now we'll add in 3 authors:

The table we'll insert rows into will have Surname, FirstName and Genre fields:

To insert into the target table those authors whose surnames contain a Y, first write and test the SELECT statement:

This will return the following 2 rows:

The rows returned by this SELECT statement.

To put these rows in our target table, just add an INSERT INTO statement above it:

This query will show the 2 rows added into the target table:

Notice that the column headings are different from those in the source table.

Using Stored Procedures to Insert Rows into a Table

You can take the results from a stored procedure and feed them into another table.  For example, suppose we create a stored procedure to list out the authors in the source table whose names contain a given letter:

You could then call this stored procedure when inserting rows into a table:

This will show the authors whose names contain an H:

The rows returned by this excerpt of SQL.

Outputting the Inserted Rows

A neat trick in SQL allows you to show selected fields from the rows inserted, using the OUTPUT keyword with Inserted.  The following SQL uses UNION ALL to add 3 rows into a new table of authors:

Notice that we output the author's id, first name and surname for each author added:

Here's what the above SQL would show:

We've chosen to output the author id, first name and last name for each row added.

All of the examples on this page add rows into an existing table, but what if you want to create a new one?  That's the subject for the final part of this tutorial.

After the complications of inserting rows into an existing table, this is refreshingly simple.  All that you do is take a simple SELECT statment:

This will return a list of the 3 authors added who aren't called Orwell:

The authors returned by this SQL.

To this, you just add an INTO clause:

We put the authors returned into a new table.

Thus if you amend the end of our SQL to read like this:

When you run it, you'll see the same rows returned, but this time from the brand new tblAuthorNew table:

The same result, but these rows come from a new table.

What Happens if the Table Already Exists

If you run the SQL shown above a second time, you'll get an error message:

You can't overwrite a table using this method.

Because of this, it can be a good idea to check the table doesn't exist before you attempt to create it from selected rows in an existing table:

And those are the ways to insert rows into a new or existing table!