Post reply

Insert and Update

  • October 8, 2008 at 10:15 am

    #199129

    Hi All

    Is there any way where i can insert and update between 2 tables at the same time using one T-SQL statement

    Cheers

    🙂

  • October 8, 2008 at 10:28 am

    #882442

    no, it'll be two separate statements....but that's not a bad thing.

    can you give more details what you are trying to do?

    this is a very common situation, where you update existing data, then insert data that wasn't there after that, based on some criteria.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 8, 2008 at 10:41 am

    #882448

    exactly i am using table a and table b

    i want to update records on table b to a and then insert records that are not on table a

    Thanks

    🙂

  • October 8, 2008 at 10:50 am

    #882451

    yeah...still two statements, you really didn't give enough info to help, but here's a lame example:

    --only matches will get updated, right?

    UPDATE TABLEA

    SET PROCESSFLAG='Y' WHERE PROCESSFLAG='N'

    FROM TABLEB

    WHERE TABLEA.ID = TABLEB.ID

    --now insert from TABLEA

    INSERT INTO TABLEA(column list)

    SELECT TABLEB.ID, 'N' AS PROCESSFLAG,other columns..

    FROM TABLEB

    LEFT OUTER JOIN TABLEA ON TABLEB.ID = TABLEA.ID

    WHERE TABLEA.ID IS NULL --null, because not in TableA yet....

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 9, 2008 at 4:43 am

    #882791

    Here comes the Microsoft answer: "Upgrade to MSSQL 2008, start using the MERGE statement" :D:D

    Wilfred
    The best things in life are the simple things

  • October 10, 2008 at 8:00 am

    #883419

    I need to make the 2 SQL statements into one, is'nt there a MERGE statement on SQL 2005 🙂

  • October 10, 2008 at 3:32 pm

    #883671

    CrazyMan (10/10/2008)

    I need to make the 2 SQL statements into one, is'nt there a MERGE statement on SQL 2005 🙂

    Just put them both in a single transaction. Commit only if both operations occur with no error.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 10, 2008 at 4:54 pm

    #883693

    CrazyMan (10/10/2008)

    I need to make the 2 SQL statements into one, is'nt there a MERGE statement on SQL 2005 🙂

    Yeah, but in 2005 it's call UPSERT.

    ...tick...

    ...tock...

    (Wait for it....)

    ...tick...

    ...tock...

    Ok, before you waste a lot of time searching the bol, UPSERT is a principle, not a SQL statement. Lowell has illustrated how it's done -- wrap the two statements in a transaction to make them one unit of work. We here can only offer what will work, not what we wished would work.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • October 13, 2008 at 5:15 am

    #884070

    Thanks Guys, I am looking forward to change to 2008, next year, I will make this as 2 statements for time being .

    Thanks for all the posts

    Cheers

    🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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