Solved

Basic T-SQL Script Failure

Posted on 2016-09-06
7
56 Views
Last Modified: 2016-09-07
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
0
Comment
Question by:waltforbes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Assisted Solution

by:Snarf0001
Snarf0001 earned 150 total points
ID: 41786851
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 300 total points
ID: 41786855
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41786878
As an aside, what's the purpose of SaleID being a uniqueidentifier instead of an int with or without an identity?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 41787023
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
 

Author Closing Comment

by:waltforbes
ID: 41787057
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41787059
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
 

Author Comment

by:waltforbes
ID: 41787867
'Hello world' - and hear ye, world: wonderful people live at Experts Exchange! Yaaay! :-D
ThumbsUpEE.PNG
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
calculate days away 11 62
TSQL remove duplicates from different columns 14 53
Question about Common Table Expressions 3 45
SQL - Aging Report - Display Months with no data 8 43
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question