Excel to SQL Table -DTS No duplicates

Question

Excel to SQL Table -DTS No duplicates

sgooch

SSC-Addicted

Points: 404
More actions
December 12, 2003 at 12:00 pm

#177375

Forgive the Newbie.
I have a DTS package that takes data in an excel sheet and puts it in a SQL table, easy enough.
I don't want to duplicate data.
If the two fields in excel are equal to a record in SQL, don't insert.
(i.e. if Sql record has (field.A=Excel.A) AND
(field.B=Excel.B) then don't insert.)
Any help would be great!
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

data_czar Ten Centuries Points: 1147 More actions · Answer 1

I can think of 2 methods. (1) Load to staging table, then select distinct and insert into main table. (2) Use the data driven query task. And OnInsertFailure Skip the row.

SeekQuel SSCommitted Points: 1818 More actions · Answer 2

You can launch:

INSERT INTO table

(A, B)

SELECT Excel.A, Excel.B

FROM Excel

WHERE NOT EXISTS

(SELECT 1

FROM table

WHERE Excel.A = Table.A

AND Excel.B = Table.B)

JPGGuy SSC Rookie Points: 31 More actions · Answer 3

quote:
I can think of 2 methods. (1) Load to staging table, then select distinct and insert into main table. (2) Use the data driven query task. And OnInsertFailure Skip the row.

OnInsertFailure could trigger for any reason, not just duplicates. No? I dont know if that is a reliable solution.

data_czar Ten Centuries Points: 1147 More actions · Answer 4

True, it could fail for any reason. But you can write the record to an error log and research it later and let your job continue to run.

sgooch SSC-Addicted Points: 404 More actions · Answer 5

Thanks for all of the input.

I like this idea:

INSERT INTO table

(A, B)

SELECT Excel.A, Excel.B

FROM Excel

WHERE NOT EXISTS

(SELECT 1

FROM table

WHERE Excel.A = Table.A

AND Excel.B = Table.B)

But being a Newbie I am not sure how to get this logic into DTS. Does anyone have time to give me some help on this? Do I do this as part of the workflow between my Excel Connection and SQL Connection?

My actual code would look like this:

INSERT INTO TestSerialFigure

(SerialNo, FigureNo)

SELECT Sheet1$.SerialNo, Sheet1$.FigurelNo

FROM Sheet1$

WHERE NOT EXISTS

(SELECT 1

FROM dbo.TestSerialFigure

WHERE Sheet1$.SerialNo = dbo.TestSerialFigure.SerialNo

AND Sheet1$.FigurelNo = dbo.TestSerialFigure.FigureNo)

Thanks again for the help!

sgooch SSC-Addicted Points: 404 More actions · Answer 6

Ended up going from Excel to a temp table, then using the "Select Where Not" statement from the temp table to the destination table. Could never seem to use the "Select Whre Not" straight from Excel to the destination table.

Thanks for all of the input!