Using the Insert Statement

Introduction

To add data to a table, we add one row at a time. This chapter introduces the basic INSERT statement, which is the primary way data is inserted into a table. We also refer to this as "populating" a table.

Inserting Data into a Table

So far, we have looked at existing data in a table using the SELECT statement, which is the only T-SQL statement used to retrieve data.

The T-SQL statement used to insert new rows or records into a table is the INSERT statement. The syntax of the INSERT statement looks like this:

insert into schema.tablename (column1, column2,…) values (value1, value2);

Example:

insert into dbo.people (fullname, age, occupation, birthdate, gender) values (‘Red Grange’,80,’Coach’,’1903-06-13’,’M’);

Syntax Rules for INSERT

  • "insert into" is required. As with SELECT, spacing and casing do not matter. But the statement must start with these keywords.
  • The schema name and table name come next. If schema is omitted, it defaults to "dbo". But best practice is to use the schema name in all cases.
  • After the table name is a list of columns, in parentheses. The parenthesis are required. The column names are separated by commas. Column names are not strings, so they do not get enclosed in quotes.
  • It does not matter what order the columns are listed.
  • A VALUES clause comes next, which is the word "VALUES" and list of column values in parenthesis - separated by commas.
  • The number of values in the VALUES clause must match the number of columns listed after INSERT.
  • The values must also match each column data type.
  • Strings and dates in T-SQL are surrounded by single quotes, and this applies to values in the VALUES clause. There must be a matching end quote for each beginning quote.

Study the image below. Each value provided in the "values" clause must match the column where it will be inserted, by position and data type.

Insert Statement Example

Figure 1: Insert Statement Example

Here is another example for the same table. Notice that the columns are in a different order, but that the values match the columns.

Insert Statement Example 2

Figure 2: Insert Example With Different Column Order

Common INSERT Errors

It is common to make syntax or other errors when creating an INSERT statement. Take a look at these common errors:

Insert Error Example 1

Figure 3: Insert Error Example With Misspelled Column Name

Insert Error Example 2

Figure 4: Insert Error Example With Invalid String

Insert Error Example 3

Figure 5: Insert Error Example With Too View Values

Insert Error Example 4

Figure 6: Insert Error Example With Too Many Values

Here are steps to take to avoid and remedy errors:

  • Look for any squiggly lines BEFORE executing the statement
  • ALWAYS check your spelling for table and column names
  • Make sure the order of the keywords is correct. Make sure VALUES is included (it is always plural - even for one column)
  • Count the number of values and make sure it matches the number of columns
  • Check to make sure the data types for each value match the data type for each column
  • Check that strings and dates are surrounded by single quotes
  • Check for missing commans and missing single quotes
  • Check for too many commas or quotes
  • Make sure the list of columns and list of values are surrounded by parenthesis

If the error message is not clear and the reason for the error is not obvious, try double-clicking on the error message. Sometimes it will highlight where the error occurred.

If necessary, simplify the INSERT statement. Try adding a single column and value. If that works, try two columns and values, etc.

Clearing a Table of Data

Sometimes we want to start all over again adding records to a table. There are three ways to do this.

DROP Table

This was covered in the previous chapter. To remove a table, it must be dropped. Dropping a table removes the table - and all the table data - from the database. In order to add new records, the table must be created again. When we are first creating a table adding test data, it is a common practice to drop the table, re-create it, and then run the INSERT statements again. With a DIE statement, as covered previously, this is a simple process. Here is a sample script:

DROP TABLE if exists xxx.mytable; CREATE TABLE xxx.mytable ( someid int not null identity(1001,1) primary key ,somename varchar(40) not null ); INSERT into xxx.mytable (somename) VALUES ('Joe Smith'); INSERT into xxx.mytable (somename) VALUES ('Mary George');

All the lines in this script can be run at once by selecting all of them and pressing F5. If the table definition needs changed, or the INSERT statements need fixed, all the changes can be executed in this manner.

Truncating the Table

A second approach to clearing a table is the TRUNCATE TABLE statement. The syntax is:

TRUNCATE TABLE xxx.mytable

The result of this statement is to remove ALL rows, but leave the current table intact. TRUNCATE TABLE does not remove the table from the database. One could execute INSERT statements after truncating the table, without having to DROP and CREATE the table.

However, TRUNCATE TABLE does do one thing similar to the DROP/CREATE process: it resets the IDENTITY seed value. In other words, if the IDENTITY column is set to start at 1001 in the CREATE TABLE statement, any new rows will start again at 1001.

Deleting Rows

The DELETE statement also remove rows. The syntax is

DELETE schema.table WHERE condition.

The DELETE statement removes individual rows in the table, based on a WHERE clause filter. If the WHERE clause is omitted, ALL rows are removed.