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.

-- Execute a built-in stored procedure to display SUPPLIERS column names

USE Northwind
EXEC sp_help suppliers
GO

Learning points

Note 1: Stored procedures need to be preceded by EXEC

Result we have a list of column names.

-- SQL statement to add Computer Performance to our suppliers

USE Northwind
INSERT suppliers
   (CompanyName, ContactName, ContactTitle,
   City, Address, PostalCode, Region, Country,
    Fax, Phone )

VALUES ('Computer Performance', 'Guy Thomas', 'Manager',
'Worcester', 'Gold Needle House', 'WR81 ', 'Midlands', 'UK',
' 01805', '07789 1341')
GO

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.

-- SQL statement to add Computer Performance to our suppliers

USE Northwind
SELECT *
FROM suppliers
WHERE companyname = 'computer performance'

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?

-- Add Computer Performance to our list of suppliers

USE Northwind
INSERT suppliers
   (CompanyName, ContactName, ContactTitle,
   City, Address, PostCode, Region, Country,
    Fax, Phone )

VALUES ('Computer Performance', 'Guy Thomas', 'Manager',
'Worcester', 'Gold Needle House', 'WR81 ', 'Midlands', 'UK',
' 01805', '07789 1341')
GO

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

2) What is wrong with the punctuation here?

-- Add Computer Performance to our list of suppliers

USE Northwind
INSERT suppliers
   (CompanyName, ContactName, ContactTitle,
   City, Address, PostalCode, Region, Country,
    Fax, Phone

VALUES ('Computer Performance', 'Guy Thomas', 'Manager',
'Worcester', 'Gold Needle House', 'WR81 ', 'Midlands', 'UK',
' 01805', '07789 1341')
GO

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.

◦

Guy Recommends: GFi EventsManager

Here is a solution to monitor, manage and archive thousands of events that are generated by devices across your entire network. Get your free evaluation copy of GFI EventsManager.

Please report a broken link, or an error.

SQL 2000 Transact - INSERT Statement

SQL Transact Insert Statement

Lesson 1 - INSERT a new customer

Learning points

Learning Points

Guy's Out Takes

SQL Transact Out Takes

See also

Guy Recommends: GFi EventsManager