Updating a Query on SQL Dashboard

[CHMS2013]

Hello and good morning. I am VERY new to this whole SQL thing.

I am trying to update a monthly sales scorecard in SQL, I have the scorecard insert opened up in SQL, as well as the info that needs to go on it however I am stumped when it comes to updating it.

This is the text on the scorecard right now:

INSERT INTO dbo.ScorecardSalesGoals

(SalesRep,Period,Goal)

VALUES('HMS','2013-4-01',70000.00)

GO


I'm stumped to where I put the sales rep, the month and the amount.

 

[SQLScholar]

This bit

VALUES('HMS','2013-4-01',70000.00)

Is inserting a record for sales rep "HMS", a period of april 2013 and, 700000 as a goal.

HMS i am guessing is a key reference to the actual name of the sales rep?



----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[CHMS2013]

Yes, I would assume that the HMS is the sales rep. All of our employees go by a 3 digit code in our system.

For example, I tried:

'TMO','2013-6-01',90000.00

and received an error.

 

[gmmastros]

It would probably be helpful to know what the error message is.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CHMS2013]

That probably would have helped. Below is the error and I've attached a screenshot if that helps.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_ScorecardSalesGoals_1'. Cannot insert duplicate key in object 'dbo.ScorecardSalesGoals'.
The statement has been terminated.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TMO'.

 

[gmmastros]

Here's my guess....

There is a primary key constraint on the ScoreCardSalesGoal table. The primary key for this table is probably SalesRep (but that's just a guess). What this means is that there cannot be any duplicates to the values in that column. You also cannot have a null value in the primary key column.

To know for sure whether the SalesRep column is the primary key, run this in a new query window.

sp_helpIndex 'ScorecardSalesGoals'

There will be at least one row, if not more. Can you copy/paste the output here?

The code you TRIED to execute is this:

INSERT INTO dbo.ScorecardSalesGoals
(SalesRep,Period,Goal)
VALUES('HMS','2013-4-01',70000.00)
GO
VALUES('TMP','2013-6-01',25500.00)

There are actually 2 errors that you got. The "Violation of PRIMARY KEY constraint" error came from this part:

INSERT INTO dbo.ScorecardSalesGoals
(SalesRep,Period,Goal)
VALUES('HMS','2013-4-01',70000.00)

The second error you got was "Incorrect syntax new 'TMO'", which came from here:

VALUES('TMP','2013-6-01',25500.00)

You see... when you use the GO keyword in SQL Server Management Studio, it's actually a "batch" separator. Meaning... it treats the first part (the part before the GO) as a separate command than the code after the GO.

To insert a new value in to the ScoreCardSalesGoals table, use the following code:

INSERT INTO dbo.ScorecardSalesGoals
(SalesRep,Period,Goal)
VALUES('TMP','2013-6-01',25500.00)

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom