We've now learned all about selecting data from a SQL server system. We've learned how to combine data results into meaningful aggregations, how to sort and filter data. However, a read-only database would be of very little benefit. The real power of SQL server comes from being able to insert, update and delete data from the system. Today, we’ll look at inserting data into SQL server.

A Word Of Warning

Up to this point in our tutorials, you haven't done anything in SQL server that can actually impact your database in any way. That is about to change. Commands like INSERT, UPDATE and DELETE are called destructive commands. That means they actually change data, for better or for worse. You'll definitely want to practice these commands in a test database where you can afford to accidently erase data or introduce new data without impacting anything important.

The Basic INSERT SQL Statement

The INSERT statement is what we use to add a new record or row to our SQL database. There are several varieties or flavors of this statement that we will talk about in this lesson. Let's start with a basic INSERT statement. We use this to enter a single new row into the database. It looks like this:

Syntax:
INSERT INTO [table name] ( [list of columns] ) VALUES ( [list of values] )
Example:
INSERT INTO Students (FirstName, LastName, Major, Address, GPA) VALUES           
(‘Jesse', ‘Ratt', ‘Science', ‘123 Fake St',  3.4)

As you can see, the first value we supply is the table name. This tells SQL which table in our database we want to add a row to. Next, we provide a list of columns we wish to fill in. You do not have to fill in every column when inserting a row. After the VALUES clause, we supply a corresponding list of values. Notice how the two line up:

Sql Insert

Let's run this new statement and see what happens in our database. Unlike a select statement, you won't see a result grid. You'll just see a message like this:

1 row(s) inserted.

Now, if we run a select on our table, we'll see that our new row has been added to the list.

SELECT * FROM Classes
Sql Insert

You'll notice that we did not supply a primary key. The database server did that for us. The great thing about SQL server is that it won't allow you to enter bad data into the system. If you make a mistake and try to enter incorrect data or forget to supply a required field, SQL will just let you know something didn't work out. This helps protect your database from bad data and corruption.

Inserting More Than One Record

You can also insert more than record at a time with multiple insert statements.

INSERT INTO Students (FirstName, LastName, Major, Address, GPA) VALUES           
(‘John, ‘Zon, ‘Science', ‘123 North St',  4)

INSERT INTO Students (FirstName, LastName, Major, Address, GPA) VALUES           
(‘Jeff, ‘Garb, ‘Science', ‘123 Vine',  2)

This statement will insert two new rows into the system. You can make as many as you want. Sometimes, though, you may want to create rows based on existing data in the database. For example, lets say we made an empty table called Student3GPA and we want to insert all the students in the Students table who have a GPA of three or more.

Let's start with defining how we would get that data with a SELECT statement:

SELECT FirstName, LastName, Major, Address, GPA
FROM STUDENTS
WHERE GPA > 3

This will give us a list of students who fit the criteria. Now, assuming our table is already made, we can convert this SELECT statement into a combo INSERT and SELECT statement with the following:

INSERT INTO Students (FirstName, LastName, Major, Address, GPA)
SELECT FirstName, LastName, Major, Address, GPA
FROM STUDENTS
WHERE GPA > 3

See what we did there? The top part is just the same as the normal single-line insert statement. However, instead of a VALUE clause, we have put a SQL statement. Notice that the columns in the INSERT statement and the columns in the SELECT statement are in the same order. This is important. The order determines which values get put into which fields.

SELECT INTO – The INSERT Shortcut

There are many database programming scenarios that require you to make a copy of an existing table. There is a cool little shortcut built into the SELECT statement that allows us to make a new table and automatically fill it with data.

Instead of making a table and then writing an INSERT statement like we did above, we can use this shortcut to quickly create a new table and fill it with data from an existing table. This is one of the coolest little shortcuts in SQL server!

SELECT *
INTO ClassCopy
FROM Class
WHERE GPA > 3

This statement accomplishes the exact same thing as the much more complex statement above. It even creates the new table for you!

Common Error Messages When Inserting

As you begin experimenting with inserting data into a SQL database, you'll probably see some of these common error messages. Here's a list of what they mean and how to troubleshoot them.

  • String Data Would Be Truncated - This means you're trying to insert something that is too big. For example, if your address field only holds 25 characters, you cannot insert a string of 50 characters. Look at your values for the offending extra-large value.
  • Required field missing. - Your VALUES clause is missing a field which must be supplied according to database rules.
  • UNIQUE or PRIMARY KEY Constraint failed uniqueness check upon INSERT. - This message is different on some systems, but anything similar to this means that you are trying to insert a primary key value that already exists. Make sure you are using unique values if you are supplying your own primary keys.

The Next Steps

Now we know some of the basics of adding new rows to our system. The next step is to learn how to update existing rows with new information. The UPDATE tutorial will show you how to do this as we delve further into SQL server's awesome capabilities.



author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories