Update from table A to table B with 100+ columns

Transact-SQL
1

Hello All,

My having a Transaction Table & an Original Main Table, I want to update the data from the Transaction Table to Original Main Table, as my tables having 100+ column.

I'm planning to have a proc, which will have the parameters like TransactionTable, OriginalMainTable, KeyColumns. currently im using dynmaic query to update the tables, is there any other easy way of doing it ?

2

Maybe this could help MERGE statement

3

Thanks,

Is there any easy way of specifying the column list for update from source to destination ? by using sysobjects ? as the scheme for both the tables are same with some join key columns

4

select table_name,column_name from information_schema.columns

OR

EXCEL sheet

if you need any help LET ME KNOW :slight_smile:

5

In my experience, if your table schema does not change very often, or not at all, it is better to simply list the columns instead of relying on any programmatic technique or dynamic SQL to list the columns. The KISS principle - Keep It Simple and Sweet.

In SSMS object explorer, right-click the table and select Script Table as -> Select To -> New Query Editor window. That will list all the columns for you. Edit it as needed, and generate your select and insert statements