April 12, 2012 at 9:07 am
There are 3 tables A, B and AB and all of them has the same data structure.
In AB, I add a primary key on MemberID column to keep MemberID unique.
I used code below to insert A and B into AB but always got a error said: cannot insert duplicate key in AB.
How to code to insert unique records into one table?
insert into AB select * from A where MemberID not in (select MemberID from AB)
insert into AB select * from B where MemberID not in (select MemberID from AB)
April 12, 2012 at 9:30 am
There must be another unique constraint on the table. Please provide DDL for the 3 tables including indexes and constraints in accordance with the directions in the article referenced in my signature by Jeff Moden.
Jared
CE - Microsoft
April 12, 2012 at 9:58 am
You have not given details of tables A and B. Assuming that tables A and B have not got a unique constraint on the memberID (as you specifically stated that it was on the AB table) then given the code as stated you would still get duplicate key inserted errors if the memberId coming from either A or B were not unique within their own table.
Example:
create table A (memberID int, Name varchar(30))
create table B (memberID int, Name varchar(30))
create table AB (memberID int primary key, Name varchar(30))
insert into A values (1,'Eric')
insert into A values (2,'Erica')
insert into A values (3,'Paul')
insert into A values (4,'Simon')
insert into B values (5,'Jack')
insert into B values (5,'John')
-- insert stage : 4 rows inserted fine as memberID 1,2,3,4 are not in AB and are unique in A
insert into AB
select * from A where MemberID not in (select memberID from AB)
-- insert stage : insert fails as B has 2 members with memberID = 5 not due to 5 already existing in AB
insert into AB
select * from B where MemberID not in (select memberID from AB)
You will need to deduplicate the memberID in the source tables to ensure that the insert succeeds. If you didn't want the second and subsequent members where the duplication exists then the code could be as below.
insert into AB
select MemberID, Name from
(select row_number() over (partition by memberID order by Name) as RN, * from B) as B2
where RN = 1 --first member with this ID
and memberID not in (select memberID from AB)
Fitz
April 12, 2012 at 10:00 am
Mark Fitzgerald-331224 (4/12/2012)
You have not given details of tables A and B. Assuming that tables A and B have not got a unique constraint on the memberID (as you specifically stated that it was on the AB table) then given the code as stated you would still get duplicate key inserted errors if the memberId coming from either A or B were not unique within their own table.Example:
create table A (memberID int, Name varchar(30))
create table B (memberID int, Name varchar(30))
create table AB (memberID int primary key, Name varchar(30))
insert into A values (1,'Eric')
insert into A values (2,'Erica')
insert into A values (3,'Paul')
insert into A values (4,'Simon')
insert into B values (5,'Jack')
insert into B values (5,'John')
-- insert stage : 4 rows inserted fine as memberID 1,2,3,4 are not in AB and are unique in A
insert into AB
select * from A where MemberID not in (select memberID from AB)
-- insert stage : insert fails as B has 2 members with memberID = 5 not due to 5 already existing in AB
insert into AB
select * from B where MemberID not in (select memberID from AB)
You will need to deduplicate the memberID in the source tables to ensure that the insert succeeds. If you didn't want the second and subsequent members where the duplication exists then the code could be as below.
insert into AB
select MemberID, Name from
(select row_number() over (partition by memberID order by Name) as RN, * from B) as B2
where RN = 1 --first member with this ID
and memberID not in (select memberID from AB)
Fitz
+1
Jared
CE - Microsoft
April 12, 2012 at 10:25 am
Thank all of you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply