Getting UPDATE statements from INSERTs

Y a h y a 416 Reputation points
2021-03-03T20:25:20.03+00:00

Hi

I have a whole bunch of INSERT statements with loads of columns that insert from Table A into Table B. Is there an easy way to get equivalent UPDATE statements that update the same columns in Table B from Table A?

So basically from

INSERT INTO TableB(ID_B, Col1, Col2, Col3)
SELECT     ID_A, [Col 1], [Col 2], [Col 3]
FROM         TableA

to

UPDATE       TableB
SET          Col1 = TableA.[Col 1], Col3 = TableA.[Col 2], Col3 = TableA.[Col 3]
FROM         ...

Thanks

Regards

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2021-03-03T22:42:10.04+00:00

    My issue is different. I am OK with syntax, IDs etc. My issue is; is there a way to batch convert a whole load of INSERTS INTO/SELECT parts of SQL statements into UPDATE/SET parts of SQL statements?

    Yes, that should be doable, but don't expect anyone to serve you a solution.

    First of all, you need to choose the language for the task. C#, Python, Perl and Powershell are all good candidates. T-SQL is most emphatically not. You will need to do a load of regular-expression matching to do this.

    There is also a semantical challenge: how to know what are the key columns of the tables, and thus go the JOIN clause rather than the SET clause. This can be deduced by reading metadata from the system tables. It is not trivial to do from the source code alone, unless you have a really simple rule as "the first column is always the key column, and there are no composite keys".

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2021-03-03T20:32:42.057+00:00

    You need a primary key column(s) or an unique column(s) in both tables. Suppose [Col 1] is the key column for both tables. Try this:

    UPDATE B
    SET B.[Col 2] = A.[Col 2], B.[Col 3] = A.[Col 3]
    FROM TableB AS B
    INNER JOIN TableA AS A ON A.[Col 1] = B.[Col 1];
    
    1 person found this answer helpful.

  2. EchoLiu-MSFT 14,576 Reputation points
    2021-03-04T05:37:23.547+00:00

    Hi @Y a h y a ,

    74124-image.png
    If you want to convert an insert/select statement to an update statement, you still need to use join like Guoxiong's method and have corresponding columns. Also, sorry I want to know why you need such a conversion.

    You can provide your actual tables and data and requirements, and there may be other solutions.

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments