SQL 2000 Transact – INSERT Statement

SQL Transact Insert Statement

Once you are familiar with SELECT, then you are ready to experiment with INSERT transact statements.

Lesson 1 – INSERT a new customer

The scenario: Your production department has just discovered a new supplier.  Our job is to enter their details into the Northwind database.

Preparation

Let us find out the column (field) names in the suppliers table.

Learning points

Note 1: Stored procedures need to be preceded by EXEC

Result we have a list of column names.

Learning Points

Note 1: INSERT firstly needs the name of the table.  Next, INSERT requires a list of fields.  VALUES takes care of the data.

Note 2: You may have noticed that I avoided the SupplierID column.  The reason was that I had inside information that the database calculates SupplierID automatically. 

Note 3: Naturally, the number and position of the column names is important.  You cannot have the VALUES in a different order, there would be no way that SQL would know that Guy Thomas is the ContactName, other than by its sequence amongst the columns.

Follow up – Let us check to see what happened.

Note 1: Transact select statements are not case sensitive, ‘Computer Performance’ or ‘computer performance’ work equally well.

Note 2: The eagle-eyed may also spot that other fields were left out.  To save typing, I omitted fields which were not important and allowed Null values.

This has been a hard example because we had to type all the values, here is a more elegant example.

Guy’s Out Takes

If you like a challenge, troubleshoot this transact statement and see if you can correct the error.

SQL Transact Out Takes

1) What is wrong with the last part of the address here?

Answer: PostALCode not PostCode.  Did you get the clue in the error message?

2) What is wrong with the punctuation here?

Answer: One missing bracket after phone).  We need to close the INSERT part of the statement before beginning the VALUES section of the transact query.

◦