Post reply

Excel to SQL Table -DTS No duplicates

  • 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!

  • December 12, 2003 at 9:07 pm

    #486056

    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.

  • December 15, 2003 at 6:08 am

    #486145

    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)

  • December 15, 2003 at 8:45 am

    #486178

    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.

  • December 15, 2003 at 9:56 am

    #486198

    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.

  • December 15, 2003 at 10:16 am

    #486203

    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!

  • December 17, 2003 at 5:46 am

    #486629

    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!

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

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