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,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 
                       FROM Table2 
                       WHERE Table2.Col1 = Table1.Col1
                            AND Table2.Col2 = Table1.Col2
                            AND Table2.Col3 = Table1.Col3)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

MehawitchiAuthor Commented:
Thank you Vitor / Guy.

Worked like charm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.