Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: insert if not exists else update

    Hello,

    it's me again
    I've got a - what I think - simple question.
    There is table A with Col1,Col2,Col3 and Table B with Col1,Col2,Col3

    I want all rows from B in A. If a row already exist in A, then update all columns, else just insert the row.
    Can someone please help me with a small syntax.
    Thank you!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First run an update on matching key values.
    Then run an insert on missing key values using WHERE NOT EXISTS or LEFT OUTER JOIN WHERE KEY IS NULL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can also try googling for UPSERT in SQL Server for more code (note - if you come across MERGE syntax - this did not make it into the final product).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2007
    Posts
    97
    Ehm, I'm still a beginner in sql :-)
    Can you please help me with Update and Insert query. I'm stuck.
    Code:
    IF not exists (select * from tblA ,tblB where tblA.col1= tblB.col1)
    
    BEGIN
       INSERT INTO tblA
       SELECT col1,col2,col3
       FROM tblB
    END
    
    ELSE BEGIN
      UPDATE tblA
      SET tblA.col2=tblB.col2
      SET tblA.col3=tblB.col3
      FROM
      tblA JOIN tblB ON tblA.col1= tblB.col1
    END
    Is this a good query for the job?

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    hmm, why not delete the rows and then insert instead of updating? Is that faster? Each row has 72 columns.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by silas
    Ehm, I'm still a beginner in sql :-)
    Can you please help me with Update and Insert query. I'm stuck.
    Getting there:
    Code:
    UPDATE tblA
    SET tblA.col2=tblB.col2
    , tblA.col3=tblB.col3
    FROM
    tblA JOIN tblB ON tblA.col1= tblB.col1
     
    INSERT INTO tblA
    SELECT col1,col2,col3
    FROM tblB
    left outer join
    tblA
    ON tblA.col1= tblB.col1
    WHERE tblA.col1 IS NULL
    I would update then insert as you will then not update the rows you just inserted. You previous sql was an either\ or - this both inserts and updates. Your syntax for the update was a tiny bit off (see the change in red). Only rows that do not exist in tblA are now inserted from tblB.

    You might want to put in a test in the update query to only update where there are differences.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2007
    Posts
    97
    This really works fine. Thank you very much!

Posting Permissions

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