Trying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist

Question

Probably best to explain this with an example.

I have 3 tables two of which are 'regular' tables and the third which is a many to many relationship table joining the first two.

I have a stored procedure that creates a temp table, Then inserts the values from the temp table into an existing table B only if they dont exist, then creates a many to many relationship between table B and the other regular table. This works fine until I hit a value that has been inserted into B previously and has an existing relationship in the many to many table as well, then I get a constraint violation. Where I'm having issues is trying to get the insert into the many to many join table to only occur if there is not existing entry.

CREATE TABLE A
    (ID INT PRIMARY KEY,
     Number INT)

CREATE TABLE B
    (ID INT IDENTITY(1,1) PRIMARY KEY,
     Name nvarchar(100))


CREATE TABLE AtoB(
[AId] [int] NOT NULL,
[BId] [int] NOT NULL,
 CONSTRAINT [AtoB_PK] PRIMARY KEY 
(
[AId] ASC,
[BId] ASC
),
CONSTRAINT FKtoA FOREIGN KEY (AId) REFERENCES A(ID),
CONSTRAINT FKtoB FOREIGN KEY (BId) REFERENCES B(ID),  
) 

some initial values

insert into A ( ID, Number) Values (1,1000),(2,1001),(3,1234);

A Working (first time) insert

DECLARE @temptable TABLE (firstname varchar(200))
DECLARE @tableAid INT
DECLARE @tableBid INT

SET @tableAid = (SELECT ID FROM A WHERE  A.Number = 1001)

INSERT INTO @temptable (firstname) VALUES ('John'),('James'),('Bill')

INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS 
( SELECT 1 FROM B WHERE B.Name = TT.firstname )

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable 
T ON B.Name = T.firstname 

The last part of this statement will fail ( as expected ) if you run it again but with some different values

INSERT INTO @temptable (firstname) VALUES ('John'),('Ted'),('Alice')

What I want to happen would be that on a second run Ted and Alice would be inserted with many to many relationships. I've tried

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable 
T ON B.Name = T.firstname WHERE NOT EXISTS 
SELECT ( * FROM AtoB JOIN B ON AtoB.BId = B.Id JOIN @temptable ON B.Name = 
@temptable.firstname WHERE AtoB.Aid = @tableAid And AtoB.BId = B.Id

and several variations but I usually end up with no values inserted into the many to many table at all.

Ideas on whats wrong?

Thanks

Long reply to answer below That means that only if none of the items exist you need to insert. Obviously, that's not what you want" - This is exactly what I want. In table B I want one one entry for each name. and this portion of the stored procedure works correctly
INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS ( SELECT 1 FROM B WHERE B.Name = TT.firstname )

The problem starts when I get to the next portion where I want to create the many to many relationship but only if its a new name value in table B. If the values passed in that fill the temptable contain an item what was previously associated between A and B the statement is terminated and the new ( and previously uninserted into B ) values do not get entered.

Answer 1

You could use NOT EXISTS as your first insert (or LEFT JOIN / IS NULL) for the second insert to work like the first, insert only when the row doesn't exist. Another way is with EXCEPT:

INSERT INTO B (Name) 
SELECT firstname FROM @temptable
EXCEPT 
SELECT Name FROM B ;


INSERT INTO AtoB (AId, BId) 
SELECT @tableAid, B.Id FROM B JOIN @temptable T ON B.Name = T.firstname 
EXCEPT
SELECT Aid, Bid FROM AtoB ;

Answer 2

The issue is in the WHERE NOT EXISTS section.
That means that only if none of the items exist you need to insert.
Obviously, that's not what you want.

So what do you need to do?

INSERT INTO AtoB (AId, BId ) 
SELECT B.Id,  @tableAid 
FROM B JOIN @temptable 
T ON B.Name = T.firstname 
LEFT JOIN ( 
SELECT ( ---insert specific columns in here

 FROM AtoB JOIN B ON AtoB.BId = B.Id JOIN @temptable ON B.Name = 
@temptable.firstname WHERE AtoB.Aid = @tableAid And AtoB.BId = B.Id) Q
ON ---What's your join?
WHERE Q.id is null

Basically what I'm saying (and this is only a draft code, but by the way you've phrased your question you can fix it in no-time):

1. make sure that you select the specific columns that are required
2. INSERT with LEFT JOIN only missing values. I always run a SELECT statement with LEFT JOIN to check that I'm inserting the correct values first.

3. Another issue: your KEY on tables A and B is based on the ID, but you do need to check that you're not entering duplicate values in tables A and B as well,

   INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS ( SELECT 1 FROM B WHERE B.Name = TT.firstname )

Should become:

INSERT INTO B (Name) 
SELECT TT.firstname 
FROM @temptable TT LEFT JOIN B ON 
 (B.Name = TT.firstname )
WHERE B.id IS NULL

Answer 3

I actually just found a solution to this buy using a second temp table. Where I store only values that do not exist in the B table and then using the second table to do the inserts so that only values without an existing entry are processed.

DECLARE @temptable TABLE (firstname varchar(200))
DECLARE @nodupetable TABLE (fname varchar(200))
DECLARE @tableAid INT


SET @tableAid = (SELECT ID FROM A WHERE  A.Number = 1001)

INSERT INTO @temptable (firstname) VALUES ('John'),('James'),('Bill')
--,('Tom'),('Harris'),('Will')    -- Uncomment this line after one execution 
to test the many to many table insert

INSERT INTO @nodupetable (fname) SELECT TT.firstname FROM @temptable TT 
WHERE NOT EXISTS ( SELECT 1 FROM B WHERE B.Name = TT.firstname )

INSERT INTO B (Name) SELECT TT.fname FROM @nodupetable TT WHERE NOT EXISTS ( 
SELECT 1 FROM B WHERE B.Name = TT.fname )

INSERT INTO AtoB (AId, BId ) SELECT @tableAid, B.Id  FROM B JOIN 
@nodupetable T ON B.Name = T.fname 

I think that I could also use a left join on the AtoB table and then check for null values to find items that did not have a row but this method seems clearer to me. Thanks for the input !!