Stored Procedure to Compare 2 table and create new records if new

Question

Hi team,

I am new to SQL, I have the concept below I want this to implement in my project, Please assist me how can I achieve this,

I have Primary Table A and secondary table B

Need to Check below,

select each ID from table A begin with counter = 1 .....till counter reach end ID
compare ID from table A with table B

If Exists
Update into table B

If not exits
insert into table B (ID)

For each new ID created in table B fill stage_number column start from 1 ends till 100

example screen shot attached from excel

Thanks..

Answer 1

You don't need to loop to insert these records and there is nothing to update, unless your example is incomplete.

DROP TABLE IF EXISTS TableA;
DROP TABLE IF EXISTS TableB;

CREATE TABLE TableA (ID INT, CreatedDate DATE, CreatedBy VARCHAR(100));
CREATE TABLE TableB (ID INT, StageNo INT, Comments VARCHAR(100));

INSERT INTO TableA VALUES 
(1,'2020-10-06','xyz'),
(2,'2020-10-07','abc'),
(3,'2020-10-08','xyz'),
(4,'2020-10-09','xyz'),
(5,'2020-10-10','xyz'),
(6,'2020-10-11','xyz')


;With NumberSequence( Number ) as
(
    Select 1 as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < 100
)
INSERT INTO TableB (ID, StageNo)
SELECT 
    a.ID,
    n.Number as StageNo
FROM TableA a
    CROSS APPLY NumberSequence n
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.ID = a.ID AND b.StageNo = n.Number)

Answer 2

Read this great article
https://sqlperformance.com/2020/09/locking/upsert-anti-pattern

Answer 3

Check if this example works at least partially:

declare @TableA as table (ID int ) /* TODO: add more columns */

insert into @TableA values
( 1 ),
( 2 ),
( 3 )

declare @TableB as table (ID int, StageNo int) /* TODO: add more columns */

select * from @TableA order by ID

---

insert into @TableB (ID, StageNo)
select ID, h1.d * 10 + h0.d + 1
from @TableA
cross apply (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) as h0(d)
cross apply (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) as h1(d)
where ID not in (select ID from @TableB)

select * from @TableB order by ID, StageNo

Answer 4

Hi @Sarath ,

Maybe you can use the trigger first, and then update tableB stageNo column:

    DROP TABLE IF EXISTS tableA  
    DROP TABLE IF EXISTS tableB  
    create table tableA  
    (id int)  
    create table tableB  
    (id int)  
    insert tableA  
    select 1  
      
    create trigger insert_trg  
    on tableA  
    for insert  
    as   
      declare @a varchar(100)               
      select @a=id from inserted  
      insert into tableB(id)values(@a)  
                
    insert into tableA(id)values(3)  
      
    select * from tableA   
    select * from tableB  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html