Not Exsists and Multi Column Primary Key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not Exsists and Multi Column Primary Key

Author

Topic

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-22 : 13:14:23

Hello,
I am hoping someone can help me with a question. Here is my situation. I am creating a table, then inserting data into that table. I need to do three inserts after I create the table, but do not want to create any duplicate records. To do this i put a primary Key on the table that is created. Then I am using the "Not Exists" to try and check to make sure the records being inserted will not violate the primary key. However, even with the not exists I get an error saying I am violating the primary Key. My primary key consists of three columns, and I am wondering if this is what is causing my issue. here is the code

INSERT INTO [Index: Weeks With Forecasts Or Sales]
( [Customer ID], [Item ID], Week, [Last Year], [Week#] )
SELECT [Reference: SO History (2 Years)].[FC Customer ID] as [Customer ID],
[Reference: SO History (2 Years)].[Item ID],
[Reference: SO History (2 Years)].WeekShip as [Week],
[Year]-1 AS [Last Year],
[Index: Week Cross Reference].[W Of Y] AS [Week#]
FROM [Reference: SO History (2 Years)] INNER JOIN [Index: Week Cross Reference] ON [Reference: SO History (2 Years)].WeekShip = [Index: Week Cross Reference].Date
Where not exists (
select *
From dbo.[Index: Weeks With Forecasts Or Sales]
where ((dbo.[Index: Weeks With Forecasts Or Sales].[Customer ID] = dbo.[Reference: SO History (2 Years)].[FC Customer ID]) and (dbo.[Index: Weeks With Forecasts Or Sales].[Week] = dbo.[Reference: SO History (2 Years)].[Forcast Week]) and (dbo.[Index: Weeks With Forecasts Or Sales]."Item ID" = dbo.[Reference: SO History (2 Years)]."Item ID")))
GROUP BY [Reference: SO History (2 Years)].[FC Customer ID],
[Reference: SO History (2 Years)].[Item ID],
[Reference: SO History (2 Years)].WeekShip,
[Year]-1,
[Index: Week Cross Reference].[W Of Y]
HAVING [Reference: SO History (2 Years)].WeekShip>'1/1/2008';

Also, here is my code for creating the table. I have left out the code for the first insert as there will not be any duplicate records on that insert and it does not have the not exists clause.

DROP TABLE [dbo].[Index: Weeks With Forecasts Or Sales]
;
CREATE TABLE [dbo].[Index: Weeks With Forecasts Or Sales](
[Customer ID] [nvarchar](50) NOT NULL,
[Item ID] [nvarchar](30) NOT NULL,
[Week] [datetime] NOT NULL,
[Last Year] [float] NULL,
[Week#] [float] NULL,
PRIMARY KEY ([Customer ID], [Item ID], [Week]))
;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 13:56:23

Maybe your select brings up more than one entry with that keyvalues?
The not exists() would not prevend that because it it only checking against records that are already in the destination table.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 13:57:34

I found your long table names hard to review, so I have used a different style:


INSERT INTO [Index: Weeks With Forecasts Or Sales] 
	( [Customer ID], [Item ID], Week, [Last Year], [Week#] )
SELECT	[Reference: SO History (2 Years)].[FC Customer ID] as [Customer ID], 
	[Reference: SO History (2 Years)].[Item ID], 
	[Reference: SO History (2 Years)].WeekShip as [Week], 
	[Year]-1 AS [Last Year], 
	[Index: Week Cross Reference].[W Of Y] AS [Week#]
FROM [Reference: SO History (2 Years)] 
	INNER JOIN [Index: Week Cross Reference] 
		ON [Reference: SO History (2 Years)].WeekShip = [Index: Week Cross Reference].Date
	LEFT OUTER JOIN dbo.[Index: Weeks With Forecasts Or Sales] AS D
		 ON D.[Customer ID] = [Reference: SO History (2 Years)].[FC Customer ID]
		AND D.[Item ID] = [Reference: SO History (2 Years)].[Item ID]
		AND D.[Week] = [Reference: SO History (2 Years)].WeekShip as [Week]
WHERE	D.[Customer ID] IS NULL	-- Does not already exist

You could give eacn table a Short Alias name, which you might find makes the code more readable:


INSERT INTO [Index: Weeks With Forecasts Or Sales] 
	( [Customer ID], [Item ID], Week, [Last Year], [Week#] )
SELECT	T1.[FC Customer ID] as [Customer ID], 
	T1.[Item ID], 
	T1.WeekShip as [Week], 
	[Year]-1 AS [Last Year], 
	T2.[W Of Y] AS [Week#]
FROM [Reference: SO History (2 Years)] AS T1
	INNER JOIN [Index: Week Cross Reference] AS T2
		ON T1.WeekShip = T2..Date
	LEFT OUTER JOIN dbo.[Index: Weeks With Forecasts Or Sales] AS D
		 ON D.[Customer ID] = T1.[FC Customer ID]
		AND D.[Item ID] = T1.[Item ID]
		AND D.[Week] = T1.WeekShip as [Week]
WHERE	D.[Customer ID] IS NULL	-- Does not already exist

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 14:02:29

In the SELECT statement..you have valued 'Week' from [Reference: SO History (2 Years)].WeekShip
but you check it against
dbo.[Reference: SO History (2 Years)].[Forcast Week] in your NOT EXISTS condition...This might be the reason...
Also..I dont think you need the GROUP BY clause..as you are grouping by all the fields in the SELECT...just include the HAVING condition in the WHERE...

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-22 : 15:15:24

quote:
Originally posted by vijayisonly

In the SELECT statement..you have valued 'Week' from [Reference: SO History (2 Years)].WeekShip
but you check it against
dbo.[Reference: SO History (2 Years)].[Forcast Week] in your NOT EXISTS condition...This might be the reason...
Also..I dont think you need the GROUP BY clause..as you are grouping by all the fields in the SELECT...just include the HAVING condition in the WHERE...

Thanks, you are corretc. My stupidity was the issue

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 15:38:37

Np. It happens.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources