Basic Query

[coolbeanbob]

Hello,

I am working through an SQL Server 2008 book. I am to the query section. I have copied the query below, word for word (I believe), but the system is showing a problem at line 10.

This is the first query I have tried to create. I don't see what the problem is?

USE BankOfFees
GO
CREATE TABLE Customers
(customer_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL)
GO
INSERT INTO Customers
VALUES (1,'Barack','Obama'),
VALUES (2,'George','Bush'),
VALUES (3,'Bill','Clinton')
GO

Here is the error message:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Customers' in the database.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'VALUES'.

[darkagn]

Once you have created the Customers table, you can't create it again unless you DROP TABLE first. You can use an ALTER TABLE query to alter the column structure of a table without losing data.

Each INSERT statement can only accept one set of VALUES. You need to write three INSERT queries to insert your three rows of data.

[coolbeanbob]

Thanks for the reply. I noticed another example (that I doesn't cause problems) uses multiple INSERT lines. I wonder why this example does not.

[BitBlt]

Correct syntax on that kind of insert statement looks like this:

INSERT INTO Customers
(customer_id, first_name, last_name)
VALUES (1,'Barack','Obama'),
VALUES (2,'George','Bush'),
VALUES (3,'Bill','Clinton')
GO

You have to include the column names that you are using in the insert.

[BitBlt]

D'oh! I just noticed there was an error in my post also. You only have to include the list of columns if you don't have the same number of columns as data provided.

Your problem was because the keyword "VALUES" shouldn't be on every line of data for the multi-record insert.

It should look like this:

INSERT INTO Customers
VALUES 
(1,'Barack','Obama'),
(2,'George','Bush'),
(3,'Bill','Clinton')
GO

My apologies for the misleading post. After all the ranting I do about testing code before posting, I should be the LAST one making a mistake like this!