Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    11

    Unanswered: UPDATE/INSERT data import query

    Afternoon all.

    I've got an interesting one for you...

    I have a product table, which i want to update using either SQL query, store procedure, or the SSIS import routine.


    ProductTbl
    ProductCode XRef
    ABC ABC123
    XYZ XYZ123
    MNO MNO123

    I have an excel document, containing new cross references (xref) which i want to use to update the ProductTbl.


    Excel document
    ProductCode XRef
    ABC ABC123
    XYZ XXX999
    MNO OOO111
    RST RST321

    Scenario 1: Where the product codes match, and the Xref's match, do nothing. This is because there is no change necessary. (Example ProductCode: ABC)
    Scenario 2: Where the product codes match, and the Xref's are different, update the Xref field in ProductTbl using the data in the Excel Document. (Example ProductCode: XYZ & MNO)
    Scenario 3: Where the product code in the Excel Document is not in the ProductTbl, append that record to the ProductTbl (Example ProductCode: RST)

    I've rattled my brain thinking about how i can do this, a store procedure seems like the obvious choice, as it caters for the use of an 'if statement'. However... i've never written a store procedure, and wouldn't know where to start.

    There may even be something to already caters for this, but to my knowledge there isn't, therefore i'd greatly appreciate someones input, suggestion or advice on this matter.

    Thanks alot in advance!

    Tom

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how many times are you going to want to do this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It sounds more like you need to use MERGE rather than IF. Import the spreadsheet data to a table in the database, then use MERGE to merge the two tables into the ProductTbl.

  4. #4
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can use merge statement to accomplish this

  5. #5
    Join Date
    Oct 2011
    Posts
    11
    Hi,

    This will probably be done on a quarterly basis, however i will require the ability to do this on an ad-hoc database.

    Ahh, never even knew MERGE existed! Is it ran as a store procedure, or can i action it through SQL?

  6. #6
    Join Date
    Oct 2011
    Posts
    11
    Quote Originally Posted by MCrowley View Post
    It sounds more like you need to use MERGE rather than IF. Import the spreadsheet data to a table in the database, then use MERGE to merge the two tables into the ProductTbl.
    Thanks for that link MCrowley, it was very useful. I'm going to use Example C to provide for most of my coding.

    I understand 90% of it, however i don't quite get the following section of code.

    Code:
    USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
           AS Source (NewName, NewReasonType)
    Could someone explain if and/or why i need to include this?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The USING clause is similar to a from clause. MERGE was designed to take a two sets of records and merge them together. The example gives a case of a hard-coded three row recordset.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by jassi.singh View Post
    Hello,

    You can use merge statement to accomplish this
    Jassi, will you please first read the given answer(s) before posting? You are only repeating an answer that MCrowley already gave yesterday.

    If you know of a better way, you should really go ahead and post that better solution. But here you were just repeating a previous answer, so your post had no added value.

    The last few days you have been posting these folow-up replies to a large percentage of "closed" threads.
    Why are you doing this? I find these posts annoying.

    You seem to know SQL and I appreciate your willingness to contribute to the forum. All contributors read the whole thread, and only reply when they can contribute something that was not posted before. An occasional collision of two posts occur, when two contributors reply at almost the same time to a thread. But we all try to avoid repeating previous posts. If you can live by this rule, I hope to read more contributions from you in the future.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Oct 2011
    Posts
    11
    Quote Originally Posted by MCrowley View Post
    The USING clause is similar to a from clause. MERGE was designed to take a two sets of records and merge them together. The example gives a case of a hard-coded three row recordset.
    I see, thanks for your help on that.

    I have got the script working, see below.

    Code:
    MERGE INTO TargetTbl USING SourceTbl ON TargetTbl.Subkey1 = SourceTbl.SubKey1
      WHEN MATCHED THEN
      UPDATE SET SubKey3 = SourceTbl.SubKey3 
      WHEN NOT MATCHED THEN
      INSERT (Prefix,A12,Updated,SubKey1,SubKey2,SubKey3,A12S) VALUES (Prefix,A12,GetDate(),SubKey1,SubKey2,SubKey3,A12S);
    I now have the requirement to add a further join to the data source. Basically, i want to also join the SubKey2 columns. I've tried using the below, but a syntax error is returned. I'm thinking this is the point where i may need to use the "USING" part of the script... It's all a learning curve.

    Code:
    MERGE INTO TargetTbl USING TargetTbl RIGHT JOIN SourceTbl ON (TargetTbl.SubKey2 = SourceTbl.SubKey2) AND (TargetTbl.SubKey1 = SourceTbl.SubKey1)
      WHEN MATCHED THEN
      UPDATE SET SubKey3 = SourceTbl.SubKey3 
      WHEN NOT MATCHED THEN
      INSERT (Prefix,A12,Updated,SubKey1,SubKey2,SubKey3,A12S) VALUES (Prefix,A12,GetDate(),SubKey1,SubKey2,SubKey3,A12S);
    Could anyone help me on this?

    Thank you!!

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe the USING clause needs a select, but I would have to experiment some to know. Change out the clause with something like
    Code:
    USING (select * 
    from TargetTbl right join
        SourceTbl on ...)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •