Duplicate key - SQL Server Forums

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

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Duplicate key

Author

Topic

Royal
Starting Member

32 Posts

Posted - 2007-02-10 : 08:12:34

Hi!
Very grateful for some advice... duplicate key.. but how do I solve it.
By Lookup ?
[OLE DB Destination [1647]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

OLE db SQL: SELECT DISTINCT convert (char(8),date,112) as day, cast(datepart(year, date) as varchar(4)) + cast(datepart(week, date) as
varchar(2)) as weeknr, cast(datepart(year, date) as varchar(4)) + Substring(convert(varchar(10), date, 121), 6, 2) as month, YEAR(date) AS year FROM Purchase

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-10 : 08:32:18

By Lookup?
Yes.

Peter Larsson
Helsingborg, Sweden

Royal
Starting Member

32 Posts

Posted - 2007-02-10 : 15:09:07

quote:
Originally posted by Peso

By Lookup?
Yes.

Thank you! So I would need to compare the key OLE db Source "convert(char(8),date,112) as day", with Purchase datecolumn "2005-02-13 00:00:00"

Lookup SQL-command would be soumething like:
Select Distinct Purchase.date, day from Purchase
where day=Purchase.date

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-10 : 21:03:23

Since your Primary Key violation is on an attempt to insert into table dbo.Dim_Date, wouldn't it make more sense to you to do the lookup against that table to verify that the row you are trying to insert is not in that table?

CODO ERGO SUM

Royal
Starting Member

32 Posts

Posted - 2007-02-11 : 04:44:30

still little confused..
I suppose I need an sql-command in Lookup.

DELETE day from dim_date where day in(Select day from date ...

Kristen
Test

22859 Posts

Posted - 2007-02-11 : 05:26:56

I think what MVJ meant was something like


INSERT dim_date (Col1, Col2, ...)
SELECT Valeue1, Value2, ...
WHERE NOT EXISTS (SELECT * FROM dim_date WHERE MyPK = ValueN)


IF NOT EXISTS (SELECT * FROM dim_date WHERE MyPK = ValueN)
INSERT dim_date (Col1, Col2, ...)
VALUES (Valeue1, Value2, ...)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources