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?
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.