SQL insert help

Question

Hello,

Can someone help me create a query that only inserts new records into a table. I've done this in SSIS using a lookup transformation but not sure how to do this in SQL.

I'm currently using something like this:

Insert into table1

select * from table2

Unfortunately, I don't have UID's and need to compare all columns in the table. I'm hoping there is better way then joining on all columns.

Any help would be greatly appreciated.

Thanks,

Tim

Answer 1

If you want to insert all rows from the source which don't have an exact match on every column in the destination table, you can do

;With cte As
(Select <column list>
From Source
Except
Select <column list>
From Destination)
Insert Destination (<column list>)
Select <column list> 
From cte;

Tom

Answer 2

hmm, the common approach for a "new record" is

INSERT INTO Destination ( ColumnList )  
            SELECT Src.ColumnList  
            FROM   [Source] Src  
            WHERE  NOT EXISTS (   SELECT *  
                                  FROM   Destination Dst  
                                  WHERE  Dst.CandidateKeyColumns = Src.CandidateKeyColumns );  

where the CandidateKeyColumns are the columns who form a candidate key, often the primary key.

A short-cut for duplicate search is using CHECKSUM(*), but it has some drawbacks.

Answer 3

Hi @Tim Mullady ,

You can use except, please refer to the following example:

--except  
create table testu1  
(C1 int,C2 int)  
insert into testu1 values(1,1),(2,2),(2,2),(3,3)  
  
  
create table testu2  
(C1 int,C2 int)  
insert into testu2 values(3,3),(4,4),(1,1)  
  
select * from testu1  
select * from testu2  
  
insert into testu2  
select * from testu1 except   
select * from testu2  
  
select * from testu2  
  
drop table testu1  
drop table testu2  

Output:

c1 c2  
3	3  
4	4  
1	1  
2	2  

If there are duplicate rows in the newly inserted data, please refer to the following code:

create table testu1  
(C1 int,C2 int)  
insert into testu1 values(1,1),(2,2),(2,2),(3,3)  
  
create table testu2  
(C1 int,C2 int)  
insert into testu2 values(3,3),(4,4),(1,1)  
  
;with except_all as  
 (select row_number()   
         over(partition by C1  
		  order by(select 0)) as rn, C1,C2  
		  from testu1  
		  except  
 select row_number()   
         over(partition by C1  
		  order by(select 0)) as rn, C1,C2  
		  from testu2)  
  
insert into testu2  
select c1,c2 from  except_all  
  
select * from testu2  
  
drop table testu1  
drop table testu2  

Output:

c1 c2  
3	3  
4	4  
1	1  
2	2  
2	2  

If you have any question, please feel free to let me know.

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.