Use Output to populate multiple values and select when not already existing [duplicate]

Question

I am trying to add information to two tables. The first table will issue an ID number that must be included in the second table, and data is only to be added to the second table if it is not already there.

Right now, I have this (needless to say, it doesn't work). The DECLARE bit is fine and returns the ID and I can use the ID if I just select it and don't want to add multiple rows or check for previous existance. Any push in the right direction would be most welcome!

DECLARE @ID Table (ID int);
INSERT INTO Table1 (FIELD1, FIELD2, FIELD3) 
OUTPUT Inserted.IDFIELD INTO @ID
VALUES(1, 2, 3);

INSERT INTO Table2 (Other1_theID, Other2, Other3) 
Select (@ID.Sequence, New.Other2, New.Other3)
from @ID, NEW
(VALUES(Sequence,'A','B'),  
(Sequence,'C', 'D'),
(Sequence,'E', 'F'),
(Sequence,'G', 'H')) as NEW
where not exists (SELECT 'X' FROM Table2 TT WHERE TT.Other3 IN ('B','D','F','H');

So, in the above case, I'm adding a new row to Table1 with the first four lines and that much works fine.:

ID  FIELD1  FIELD2  FIELD3  
1     5       8       6    <existing data
2     9       3       2    <existing data
3     4       1       7    <existing data
4     1       2       3    <my new row
^ the new ID automatically assigned by the system when the new row is added to the table

In the second bit, I need to add to a second table some data associated with the first (so I need to bring the ID along). If I use this code to bring in one row of data, it works:

INSERT INTO Table2 (Other1_theID, Other2, Other3) 
Select ID, A, B from @ID

Other1_theID    Other2  Other3  
    4             A       B       <my new row
    ^ the new ID I go from the Table1 insertion.

All of that's great, but I really want to add several rows to Table 2 AND I only want to add the if they don't already exist (if A, B exist in Other2 and Other3 with a different ID, then don't add them).

I don't understand, can you provide a minimal reproducible example with sample data and desired results. — Dale K
– Dale K, Commented Aug 22 at 20:30
Second query seems should be MERGE operation. However, without an example of the data and the desired result, it is difficult to understand what you want to achieve. — ValNik
– ValNik, Commented Aug 22 at 21:45

Silver · Accepted Answer · 2025-08-29 14:33:52Z

0

Solved. There might be a more elegant way, but this worked:


DECLARE @ID Table (ID int);
INSERT INTO Table1 (FIELD1, FIELD2, FIELD3) 
output Inserted.IDFIELD INTO @ID
Select 1,2,3
    where not exists (SELECT 'x' FROM Table1 T1 WHERE T1.FIELD1 = 1 AND T1.FIELD2 = 2;
INSERT INTO Table2 (Other1_theID, Other2, Other3) 
(Select ID,'A','B'from @ID
    where not exists (SELECT 'x' FROM Table2 T2 WHERE T2.Other2 = 'A' AND T2.Other3 = 'B')) UNION ALL
(Select ID,'C','D'from @ID
    where not exists (SELET2 'x' FROM Table2 T2 WHERE T2.Other2 = 'C' AND T2.Other3 = 'D')) UNION ALL
(Select ID,'E','F'from @ID
    where not exists (SELET2 'x' FROM Table2 T2 WHERE T2.Other2 = 'E' AND T2.Other3 = 'F'))

answered Aug 29 at 14:33

Silver

13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Aug 29 at 14:38

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Use Output to populate multiple values and select when not already existing [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related