SQL Server Performance

SSIS using excel source to update/insert sql table

Discussion in 'SQL Server 2005 Integration Services' started by SQL_Guess, Aug 29, 2006.

  1. SQL_Guess New Member

    Hi all,

    I've had little success gooling/searching for this (so far).

    Given a simple spreadsheet:
    StoreNumber StoreName
    1 UPDStoreName_1
    2 UPDStoreName_2
    3 UPDStoreName_3
    4 NEWStoreName_4

    I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))
    StoreNumber StoreName
    1 StoreName_1
    2 StoreName_2
    3 StoreName_3
    5 StoreName_5

    .. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :

    StoreNumber StoreName
    1 UPDStoreName_1
    2 UPDStoreName_2
    3 UPDStoreName_3
    4 NEWStoreName_4
    5 StoreName_5

    (the UPD and NEW are added to simplify the example).

    Now the default action of an excel source into an ole db destination is an insert into the table - so PK constraints causes failures.

    Now, given that the table is referred to by other table, and is in a 24x7 website, how do I change the SSIS package such that, on a row-by-row basis, anUpSert (update or insert) is performed?

    The only idea I have so far is:

    create temp table
    insert excel data into temp table
    iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP

    Isn't there a better way?



    Panic, Chaos, Disorder ... my work here is done --unknown
  2. Madhivanan Moderator


    General Approach for UPSERTs

    Update T
    set col1=S.col1, col2=S.col2,...
    from TargetTable T inner join SourceTable S
    on T.keycol=S.keycol

    Insert into TargetTable(columns)
    Select columns from SourceTable S
    where not exists(Select * from TargetTable where keycol=S.keycol)

    You can use EXCEL source in place of Source Table

    eg
    http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=8440

    Madhivanan

    Failing to plan is Planning to fail

Share This Page