Link to home
Start Free TrialLog in
Avatar of jdallen75
jdallen75Flag for Canada

asked on

Archiving data - primary key violation

In SQL Server 2005, what can cause a primary key violation on insertion of records into a table, BESIDES inserting records with duplicate data on that primary key?
 
I have a SQL table with a primary key on one field called lOEEEventId (type int). I'm copying records from that one table to an identical archive table (same server, different DB), which has the same primary key, and is empty to start with.
 
Furthermore, I've done a count(distinct lOEEEventId) and count(lOEEEventId) on the original [Bizware]table, and it returns the same number, suggesting there are no duplicates - which shouldn't be allowed on a primary key anyways.

OEEEvent and OEEEventFlexData each have about 44 million records. @ArchiveDataOlderThan set to 3 months (about 33 million to move, 10 million to stay).

set @rowcnt=(select count(*) from [BizWare].[BizWareUser].[oeeeventflexdata]
	where loeeeventid in (select loeeeventid from [BizWare].[BizWareUser].[oeeevent] where tstart < @ArchiveDataOlderThan))
while @rowcnt>0
begin
	if @rowcnt < 1000000
		begin
			set rowcount @rowcnt
		end
	else
		set rowcount 1000000
	
	insert into [BizWare_Archive].[BizWareUser].[oeeeventflexdata]
		select * from [BizWare].[BizWareUser].[oeeeventflexdata]
		where loeeeventid in (select loeeeventid from [BizWare].[BizWareUser].[oeeevent] where tstart < @ArchiveDataOlderThan)
 
	delete from [BizWare].[BizWareUser].[oeeeventflexdata]
		where loeeeventid in (select loeeeventid from [BizWare].[BizWareUser].[oeeevent] where tstart < @ArchiveDataOlderThan)
 
	set @rowcnt=@rowcnt-1000000
end

Open in new window

Archive-Result-Screenshot.jpg
Avatar of chapmandew
chapmandew
Flag of United States of America image

A primary key violation is a violation of a unique constraint.  So, you are trying to enter duplicate rows.

One thing to note is that in SQL Server, two null values are NEVER the same value....but, when you try to enter two null values into a unique constraint, it will be violated.  That is one thing I've never liked about SQL Server.

Consider changing your query to:

      insert into [BizWare_Archive].[BizWareUser].[oeeeventflexdata]
            select * from [BizWare].[BizWareUser].[oeeeventflexdata]
            where loeeeventid in (select loeeeventid from [BizWare].[BizWareUser].[oeeevent] where tstart < @ArchiveDataOlderThan) and loeeventid not in(select loeeeventid from [BizWare_Archive].[BizWareUser].[oeeeventflexdata])
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jdallen75

ASKER

Good point... I'll add that to the query and try.
About nulls: the primary key (lOEEEventId) is set up to NOT allow nulls.
Also, would a null value not be captured in count and count (distinct)?
That is good on not allowing nulls....typically they are not allowed....unless it is a composite key, and one of the fields allows it....that goes for a unique constraint...I think PK do not allow nulls at all...so, you're good there.

About count()...it depends on the nulls.  If you do a count(fieldname) and the fieldname allows nulls, then the count will be only for those records that do not contain null values (for that field only).  If  you do a count(*), it is going to count every record returned in the result set, regardless of nulls.

IF you include the logic that I included, then you are making sure that you are not trying to insert records that already exist, and it should fix your code....assuming that the resultset you are trying to enter does not contain duplicate values for your primary key fields.

HTH,
Tim
Sorry guys... the first code snippet I attached in fact works without the primary key violation.
It is the second snippet attached (a more *restrictive* query) which does not. This seems strange to me, as I'd expect the second recordset to really be a subset of the first. [Background: there is a classification of product called safety stock, which I had to filter out from archiving since it is required to stay in the production database regardless of age.]

First one that DOES work:
**************
insert into [BizWare_Archive].[BizWareUser].[oeeeventflexdata]
		select * from [BizWare].[BizWareUser]. oeeeventflexdata]
		where loeeeventid in (select loeeeventid from [BizWare].[BizWareUser].[oeeevent] where tstart < @ArchiveDataOlderThan)
**************
 
Second one that DOES NOT work (primary key violation):
**************
insert into [BizWare_Archive].[BizWareUser].[oeeeventflexdata]
		select * from [BizWare].[BizWareUser].[oeeeventflexdata]
		where loeeeventid in (
			select loeeeventid from [BizWare].[BizWareUser].[oeeevent] 
			where tstart < @ArchiveDataOlderThan
			)
		and dFlex3Numeric not in (
			select TrackingNumber from [BizWare].[dbo].[Cus_SafetyStock]
			)
**************

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Angellll,
On your OUTPUT suggestion, do I need to:
(A) OUTPUT inserted.* into [a temp table], then delete from the original Bizware the contents of this table, or
(B) Can I delete the OUTPUT directly somehow?

Thanks for the article... very useful