How To Insert Non-Duplicate Rows into SQL Table

Hello Experts,

I have an SSIS package that reads from Excel File and loads the data into a SQL table.

The SQL table has a Unique constraint comprised of three columns (Category, Genre and SubGenre), so when I attempt to load the SQL table from the Excel table, the entire insert statement is terminated because some of the records in the Excel table already exist in the SQL table.

I need to build a query or (some logic) to extract only the rows that don't exist in the SQL table. Something along the following:

Insert Into Table2 (Col1, Col2, Col3)
(SELECT Col1, Col2, Col3 FROM Table1 WHERE NOT EXIST in Table2)

Many thanks for your help,
Hani
MehawitchiAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can import to a temporary table (Table1) in SQL and then use the following command to insert into the target table (Table2):
Insert Into Table2 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 
FROM Table1 
WHERE NOT EXIST (SELECT 1 
                       FROM Table2 
                       WHERE Table2.Col1 = Table1.Col1
                            AND Table2.Col2 = Table1.Col2
                            AND Table2.Col3 = Table1.Col3)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:

Insert Into Table2 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 
  FROM Table1  t1
WHERE NOT EXIST ( select null from Table2 t2 where t2.col1 = t1.col1 and t2.col2 = t1.col2 and t2.col3 = t1.col3)
group by Col1, Col2, Col3 

Open in new window

0
 
MehawitchiAuthor Commented:
Thank you Vitor / Guy.

Worked like charm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.