locked
Task to Update Table based on SQL from View RRS feed

  • Question

  • Dear Forum,
    I have a View which has more than a million records and have optimised the Query to the best of my knowledge but still its timing out.
    What I want to do now is update a table by running the View and capturing the return values.
    What is the best way of doing this?

    I am not an expert in T-SQL so please be a little patient when you help me !!!

    Best Regards
    Franklin William

    • Moved by Phil Brammer Thursday, February 4, 2010 3:37 PM Moving to the Transact-SQL forum (From:SQL Server Database Engine)
    Thursday, February 4, 2010 2:43 PM

Answers

  • It will be best to post the table and view structures, the relation columns, and which columns you are trying to update. Here is one example:

    WITH UpdateCTE AS (
    SELECT T.datacol1, T.datacol2, 
           V.datacol1 AS new_datacol1, 
           V.datacol2 AS new_datacol2
    FROM MyTable AS T
    JOIN MyView AS V
      ON T.keycol = V.keycol)
    UPDATE UpdateCTE 
    SET datacol1 = new_datacol1,
        datacol2 = new_datacol2; 

    Plamen Ratchev
    • Marked as answer by Zongqing Li Monday, February 15, 2010 7:10 AM
    Thursday, February 4, 2010 3:44 PM