Insert in to table without duplicate records TSQL

Question

Team,

Need some assistance with TSQL

table1

ID |Name| pin
1    a     101
2    b     201
3    c     301
4    d     401

table 2

ID|Name|pin
3   c   301
4   d   401
5   e   501
6   f   601

I want to insert to table1 from table2 only those values which are not duplicates (for all columns in table 2).

Final output in table1

ID | Name | pin
1    a      101
2    b      201
3    c      301
4    d      401
5    e      501
6    f      601

Appreciate your response. Thanks

Answer 1

INSERT INTO table1 (ID, Name, pin)
SELECT table2.ID, table2.Name, table2.pin
FROM table2
LEFT JOIN table1 ON t1.Name=t2.Name
WHERE t1.Name IS NULL

or

INSERT INTO table1 (ID, Name, pin)
SELECT ID, Name, pin
FROM table2
WHERE NOT EXISTS ( SELECT NULL
                   FROM table1 
                   WHERE t1.Name=t2.Name )

If table1.ID value is an identity and must not be inserted but generated then replace ID in SELECT with NULL.

Answer 2

You could also get it using except.

drop table if exists table1
go
create table table1(id int, name char(1), pin int)
go
drop table if exists table2
go
create table table2(id int, name char(1), pin int)
go
insert into table1(id, name, pin) values
(1, 'a', 101),
(2, 'b', 201),
(3, 'c', 301),
(4, 'd', 401)
go
insert into table2(id, name, pin) values
(3, 'c', 301),
(4, 'd', 401),
(5, 'e', 501),
(6, 'f', 601)
go

--Insert into table1 without duplicates
insert into table1 
select * from table2
except 
select * from table1