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.
Let us find out the column (field) names in the suppliers table.
Note 1: Stored procedures need to be preceded by EXEC
Result we have a list of column names.
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.