getting violation of PK (duplicate records) error
-
Hi!!!
I have two tables Etable1 and Etable2.
Etable2 has columns like ID VARCHAR(12), CreateDate DateTime and ModifiedDate DateTime (Default Value is GETDATE()) I set Primary Key Constraint on Both ID and CreateDate columns for Etable2.
In Etable1 there is no such constraints..so there may be duplicate values....or values may be like that ID and CreateDate same but ModifiedDate is different..here I want Max(ModifiedDate)
create table Etable1
(ID Varchar(10),CreateDate DateTime,ModifiedDate Datetime Default GetDate())
create table Etable2
(ID Varchar(10),CreateDate DateTime,ModifiedDate Datetime Default GetDate(),
CONSTRAINT [PK_IDCD] PRIMARY KEY CLUSTERED
(
ID,
CreateDate
)
) ON [PRIMARY]
insert into Etable1 (ID,CreateDate,ModifiedDate)
Values ('28','2005-08-30 02:41:00.000', '2005-09-22 13:31:16.917')
insert into Etable1 (ID,CreateDate,ModifiedDate)
Values ('28','2005-08-30 02:41:00.000', '2005-09-22 13:31:16.917')
insert into Etable1 (ID,CreateDate,ModifiedDate)
Values ('22','2005-08-30 02:41:00.000', '2005-09-22 13:31:16.917')
insert into Etable1 (ID,CreateDate,ModifiedDate)
Values ('22','2005-08-30 02:41:00.000', '2005-09-22 13:35:16.917')
insert into Etable1 (ID,CreateDate,ModifiedDate)
Values ('29','2005-08-30 02:41:00.000', '2005-09-22 13:48:16.917')
insert into Etable1 (ID,CreateDate,ModifiedDate)
Values ('30','2005-08-31 02:41:00.000', '2005-09-22 13:52:16.917')
insert into Etable1 (ID,CreateDate,ModifiedDate)
Values ('30','2005-08-31 02:41:00.000', '2005-09-22 13:59:16.917')
/* My Query*/
INSERT INTO Etable2 (ID,CreateDate,modifieddate)
SELECT ID,CreateDate,Etable1 .modifieddate FROM Etable1 INNER JOIN
(SELECT (ID + CAST( CreateDate AS VARCHAR(40))) AS PK, max(modifieddate) as Modifieddate FROM Etable1
GROUP BY ID,CreateDate) B
ON (Etable1.ID + CAST(Etable1.CreateDate AS VARCHAR(40))) = B.PK AND etable1.Modifieddate = B.Modifieddate
AND (ID + CAST( CreateDate AS VARCHAR(40)))
NOT IN ( SELECT (ID + CAST( CreateDate AS VARCHAR(40))) FROM Etable2)
But still I am getting error like "Violation of Primary Key Constraints .Cannot Insert Duplicate Records"
Why????
May be due to concatenating of different datatype or due to DateTime Function of column or something else?????????????
or shall i change my query.......but i think my query is right!!!!
T.I.A
Shashank
Regards,
Papillon
-
You've got two completely identical records in ETable1. While your subquery is correctly retrieving no duplicates, as soom as joi do the join back to Etable1, there's a dup sneaking in. From what you posted I think the query can be simplified to
SELECT ID,CreateDate,modifieddate
FROM (SELECT ID, CreateDate, max(modifieddate) as Modifieddate FROM Etable1
GROUP BY ID,CreateDate) AS B
WHERE NOT EXISTS (SELECT 1 FROM Etable2 WHERE B.ID=Etable2.ID AND B.CreateDate=Etable2.CreateDate)
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hey GilaMonster !!!!!
I really appreciate what you did, just one little thing
that if i have more columns in both table that are not having primary constraint then what modification should i make in the query... for example suppose i have columns like name,rollnumber,cardtype,housename like that along with ID,CreateDate and modifieddate
T.I.A
Shashank
Regards,
Papillon
-
Depends what you want to do with the records.
If you have 2 records with the same ID, createdate, modified date and different cardtypes, then which one do you take?
What about same ID, createdate but different modification date and different card types.
The problem you encountered was de to two records with exactly the same id, createdate, modification date.
Unfortunatly eliminating duplicate data is not easy and methods depend on exactly what you want to do with the dup data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi!!!
Thanks for your patience!!!!!!!!!!!
I know the values in the columns other than PK columns , having different values then that are not eliminated (Duplicate Records issue) but if the values are same like two rows are exactly identical then in case shall I take query like below
SELECT ID,CreateDate,name,rollnumber,cardtype,housename,modifieddate
FROM (SELECT ID, CreateDate,name,rollnumber,cardtype,housename, max(modifieddate) as Modifieddate FROM Etable1
GROUP BY ID,CreateDate,name,rollnumber,cardtype,housename) AS B
WHERE NOT EXISTS (SELECT 1 FROM Etable2 WHERE B.ID=Etable2.ID AND B.CreateDate=Etable2.CreateDate)
FeedBack whatever
T.I.A
Shashank
Regards,
Papillon
-
Looks fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi..I got that one
Can you tell me what "WHERE NOT EXISTS (SELECT 1 FROM Etable2 WHERE B.ID=Etable2.ID AND B.CreateDate=Etable2.CreateDate)"
Do exactly here.......
any documents @ WHERE NOT EXISTS function you have for that???
Regards
Shashank
Regards,
Papillon
-
Look in Books online. Exists keyword under Transact-SQL reference.
Essentially I'm saying check Table2 for any records that have an ID, CreateDate combo that is in table 1 and exclude them.
It's much the same as doing a left join to table2 on ID and CreateDate and only picking records where there's no match.
Does the same as your original query's NOT IN, but a bit more elegantly.
Or in simple terms, don't insert records that are already there.
Does that make sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply