Copy the difference of records to another

Transact-SQL
1

Hi,
Consider this scenario where I have two tables T1 and T2 of same structure whose primary key is 'ordernum'. T1 contains 15 records and T2 contain 10 records . Is it possible to copy the extra 5 records last entered in T1 to T2 table using insert...select statement so that it insert only records that are not present
in T2.

Thanks

2

can you try this
select * from t1 where ordernum not in(select ordernum from t2)

3

Take your pick :smile:

-- ahmeds08's method
INSERT INTO T2
SELECT T1.*
FROM T1
WHERE T1.ordernum not in (select ordernum from T2)

INSERT INTO T2
SELECT T1.*
FROM T1
WHERE NOT EXISTS (select * from T2 WHERE T2.ordernum = T1.ordernum)

INSERT INTO T2
SELECT T1.*
FROM T1
     LEFT OUTER JOIN T2
         ON T2.ordernum = T1.ordernum 
WHERE T2.ordernum IS NULL

I think (2) performs better than (1), but I may be wrong, and (2) and (3) work out as being the same

I use the last way. Probably the most "obtuse" to read, until you get used to it, but I find that the code is more commonly repeated with if there are also UPDATE steps involved e.g.

UPDATE T2
SET T2.col1 = T1.col1, ...
FROM T1
     JOIN T2
         ON T2.ordernum = T1.ordernum
4

INSERT INTO T2
SELECT T1.*
FROM T1

except

SELECT T2a.*
FROM T2 T2a

This might perform better...