Unique Constraint Violation when already checked using WHERE clause 1

[Klae]

Hello

I'm pretty up on my MS T-SQL skills but this one is beating me! I have a table with a PK and the rest of the fields are part of a Unique Constraint. When I INSERT data into this table from the flat table (import is not normalized, SQL Job runs procedures to normalize) I have made sure I have used NOT IN ... and WHERE clauses to make sure it does not try and insert duplicate data. My code logic works on other tables except one. The INSERT statement thinks it is unqiue so it goes to insert but the table thinks it is NOT unique and violates the constraint. I have checked some columns and there is an extra 2 invisible characters in some columns. So, I though Ahh Chr(10) & Chr(13)! But doing a check there isn't! Dammit. Now I'm thinking Thanks MS SQL, one part of SQL is getting a different idea of the data than another part of SQL and the whole INSERT for this table fails due to this violation. Can anyone help?

Klae

You're only as good as your last answer!

 

[SQLDenis]

probably null values
instaed of not in use not exists or a left join to be safe

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLSister]

It is also possible that the dupped data is in the records to be inserted and not already in the existing table. Make sure your code accounts for that.

Questions about posting. See faq183-874

 

[Klae]

Thanks SQLSister and SQLDenis, I've got SELECT DISTINCT for the data to be inserted so I think I've accounted for that. I can see I might need to include the snippet of SQL tomorrow (I'm in UK so its not work hours) because without you helpful people seeing it I'm not entirely sure I know what table SQLDenis means to be on the left in a LEFT JOIN. Please advise tomorrow. Ta.

Klae

You're only as good as your last answer!

 

[Klae]

OK so my original snippet is ...

[highlight]
INSERT INTO xemFileStore ([FileName], xemProduct_FK, CRC32, FileSize, FileVersion, LinkDate, [Description])
SELECT DISTINCT nd.[FileName], xp.Product_PK, nd.CRC32, nd.FileSize, nd.InternalFileVersion, nd.LinkDate, nd.InternalFileDescription
FROM NewData nd
JOIN xemManufacturer xm ON (xm.Name = nd.InternalCompany)
JOIN xemProduct xp ON (xp.Title = nd.InternalProduct AND xp.xemManufacturer_FK = xm.Manu_PK)
WHERE nd.[FileName] NOT IN (
SELECT xfs.[FileName] FROM xemFileStore xfs
[tab]WHERE nd.[FileName] = xfs.[FileName]
[tab]AND xp.Product_PK = xfs.xemProduct_FK
[tab]AND nd.CRC32 = xfs.CRC32
[tab]AND nd.FileSize = xfs.FileSize
[tab]AND nd.InternalFileVersion = xfs.FileVersion
[tab]AND CAST(nd.LinkDate AS DateTime) = xfs.LinkDate
[tab]AND nd.InternalFileDescription = xfs.[Description])
[/highlight]


So would my new LEFT JOINED code be this? ...

[highlight]
INSERT INTO xemFileStore ([FileName], xemProduct_FK, CRC32, FileSize, FileVersion, LinkDate, [Description])
SELECT DISTINCT nd.[FileName], xp.Product_PK, nd.CRC32, nd.FileSize, nd.InternalFileVersion, nd.LinkDate, nd.InternalFileDescription
FROM NewData nd
[tab]JOIN xemManufacturer xm ON (xm.Name = nd.InternalCompany)
[tab]JOIN xemProduct xp ON (xp.Title = nd.InternalProduct AND xp.xemManufacturer_FK = xm.Manu_PK)
[tab]LEFT JOIN xemFileStore xfs ON (nd.[FileName] = xfs.[FileName]
[tab][tab]AND xp.Product_PK = xfs.xemProduct_FK
[tab][tab]AND nd.CRC32 = xfs.CRC32
[tab][tab]AND nd.FileSize = xfs.FileSize
[tab][tab]AND nd.FileVersion = xfs.FileVersion
[tab][tab]AND CAST(nd.LinkDate AS DateTime) = xfs.LinkDate
[tab][tab]AND nd.InternalFileVersion = xfs.[Description])
WHERE
[tab]xfs.[FileName] IS NULL
[tab]AND xfs.xemProduct_FK IS NULL
[tab]AND xfs.CRC32 IS NULL
[tab]AND xfs.FileSize IS NULL
[tab]AND xfs.LinkDate IS NULL
[tab]AND xfs.[Description] IS NULL
[/highlight]


Klae

You're only as good as your last answer!

 

[Klae]

Oh I've noticed a couple of typo with FileVersion fields ignore them.

Klae

You're only as good as your last answer!

 

[Klae]

OK I've tried both Queries and they both try to insert the identical 242 rows. So there is no difference, I'm still getting unique data that translates to data breaking a Unique constraint. Does anyone know a way of debugging it so it tells me which row of data violates the constraint?

Klae

You're only as good as your last answer!

 

[Klae]

OK SQLDenis, it was a NULL The business wanted the LinkDate of each file recorded even if it was NULL so I had to allow it to be null and the INSERT Clause did not think NULL = NULL whereas the UNIQUE Constraint does see NULL = NULL so I have changed...

AND CAST(nd.LinkDate AS DateTime) = xfs.LinkDate

To...

AND ISNULL(nd.LinkDate, CAST('01/01/1900' AS DateTime)) = ISNULL(xfs.LinkDate, CAST('01/01/1900' AS DateTime))

its happy. It all works thanks people!

Klae

You're only as good as your last answer!