Basic T-SQL Script Failure

Points of My Scenario:

1. I am studying T-SQL as a newbie/novice.

2. I have successfully created three tables as per commands in attached SQL script, CREATE-Tables.txt

3. I attempt to perform a data insert into the Sales table with the following SQL statement: " INSERT Sales SELECT NEWID(),1, 1, 4, '02/01/2012' " - without the quotes (" "), of course! :-)

4. I receive the following error: "Msg 213, Level 16, State 1, Line 1. Column name or number of supplied values does not match table definition."

QUESTION/REQUEST: Please correct the syntax in my T-SQL statement or advise me what I am doing wrongly. Remember that I am a newbie! :-)
CREATE-Tables.txt
waltforbesSenior IT SpecialistAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Your CREATE TABLE Sales script has four columns, and your INSERT INTO Sales script has five columns.
An INSERT has to have an equal number of columns in the INSERT clause and the SELECT clause.

CREATE TABLE Sales (
	SaleID uniqueidentifier NOT NULL,
	ProductID int NOT NULL,
	EmployeeID int NOT NULL,
	Quantity smallint NOT NULL)

INSERT Sales
SELECT NEWID(), 1, 1, 4, '02/01/2012'  

Open in new window

Since I don't see a date column, guessing you want to do this..
INSERT INTO Sales(SaleID, ProductID, EmployeeID, Quantity) 
VALUES (NEWID(), 1, 1, 4) 

Open in new window

Also, if you're inserting specific values you have to use VALUES instead of SELECT.
0
 
Snarf0001Commented:
You have more columns in your insert than the table has, as per the error message.
The table you defined for sales has 4 columns:

SaleID
ProductID
EmployeeID
Quantity

But your insert statement also has a date appended to the end which doesn't exist on the table.
Inserting 5 columns into a table with 4.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
As an aside, what's the purpose of SaleID being a uniqueidentifier instead of an int with or without an identity?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
PortletPaulfreelancerCommented:
NB: It is "best practice" to specify the columns within the insert statement. If you do not do that then you will have problems aligning the data to the correct table column.

Another matter you should learn early is to NEVER assume mm/dd/yyyy is the date firmat. In TSQL YYYYMMDD is the safest.
0
 
waltforbesSenior IT SpecialistAuthor Commented:
You guys are so awesome here! You have totally resolved my issue. I am happy & very grateful.
I decided to add another column to the Sales table named 'PurchaseDate' with the data type of datetime.

@Jim Horn: I used the uniqueidentifier data type in the Sales table, just for experimentation with data types. I am studying with CBT Nuggets, and was going along with the lesson. Alas! they supplied bad scripts! It wasn't even in my paradigm that it was possible that their script was wrong - after all, they demonstrated it! Oh well.

Thank goodness for Experts Exchange! I am much grateful.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.  Most places I've seen will use an int identity(1,1) and it's good enough.  That and it's four bytes in size vs. 16-bytes for a GUID, so the GUID takes up four times the memory and would take longer to load.

Thanks for the split, good luck with your learning.  Feel free to ask questions here anytime.
0
 
waltforbesSenior IT SpecialistAuthor Commented:
'Hello world' - and hear ye, world: wonderful people live at Experts Exchange! Yaaay! :-D
ThumbsUpEE.PNG
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.