Getting UPDATE statements from INSERTs

Question

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

Answer 1

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".

Answer 2

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];

Answer 3

Hi @Y a h y a ,

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.