Post reply

Select Records from table and insert it to another table or update if exist

  • May 13, 2012 at 1:58 pm

    #274066

    I want to select Records from table A and add it or update if exist in table b

    Table A

    StudentID,StudentName,Age,TotalGrades

    Table B

    StudentHistoryID,StudentID,StudentName,Age, TotalGrades

    select all records from Table A under any condition and insert the records again into table B if no related Record for Student ID ...if found Record Related to StudentID so Update The Record and not insert new one

    Note : The Records which will be Retrieved from Table A and will use Them to insert or update will be more than one ..and may have new records not inserted before into b ...and may have exist Related Record to Student ID

    So how can i do that

  • May 13, 2012 at 2:12 pm

    #1486889

    want to select Records from table A and add it or update if exist in table b

    Table A

    StudentID,StudentName,Age,TotalGrades

    Table B

    StudentHistoryID,StudentID,StudentName,Age, TotalGrades

    select all records from Table A under any condition and insert the records again into table B if no related Record for Student ID ...if found Record Related to StudentID so Update The Record and not insert new one

    Note : The Records which will be Retrieved from Table A and will use Them to insert or update will be more than one ..and may have new records not inserted before into b ...and may have exist Related Record to Student ID

    I'll give you a hint: write two queries - one for the insert, one for the update. run them both from the same stored procedure.

  • May 13, 2012 at 2:28 pm

    #1486890

    This would be a good use for the "new" MERGE command. Take a look at it in Books Online. It's petty simple to use.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 13, 2012 at 5:08 pm

    #1486900

    could you give me any example ??

  • May 13, 2012 at 5:48 pm

    #1486902

    Heh... did you look it up in Books Online as I suggested? It has examples there. What I'm hoping is that you'll take a minute and teach yourself something new. It'll only take you a couple of minutes of study. Go for it because this is one of those "basic" things you need to know to be successful! 😉 At least try it on your own.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 13, 2012 at 10:48 pm

    #1486929

    Merge should help u...

    MERGE INTO TABLE_NAME USING table_reference ON (condition)

    WHEN MATCHED THEN

    UPDATE SET column1 = value1 [, column2 = value2 ...]

    WHEN NOT MATCHED BY Source

    INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...

    WHEN NOT MATCHED BY Destination

    INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...

    [ <output_clause> ]

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

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