• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 25
  • Last Modified:

Update table 2 from table 1 on ID match

Hi Experts,

I have a sql server table say test1 with 20-25 columns.
I have another table test2 with say 25-30 columns.
There are around 20 matching columns between the two.
Now I want to update test2 from test1 data (only matching columns need to be updated) based upon the ID column which is common to both tables.
I need a generic query where the need to specify column names is not there.

Thanks!!
0
Devildib
Asked:
Devildib
  • 7
  • 6
  • 4
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<air code>
UPDATE t2
SET t2.goo = t1.goo, t2.foo = t1.foo, t2.boo = t1.boo -- you get the idea.. 
FROM test2 t2
   JOIN test1 t1 ON t2.ID = t1.ID

Open in new window

0
 
DevildibAuthor Commented:
Thanks Jimbo for the quick turn around.But, I am looking for a generic approach where the need to specify column names is not there
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> where the need to specify column names is not there
Give us more details on what you mean, as SQL Server does not have any functionality to update 'any column not matching between two tables' without explicitly defining them, so the answer is very likely 'You can't do that'.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorr,y, i got your restriction now.


the answer is: not possible with simple sql.
dynamic sql is what you would need.
usually i would not go that way unless this is to be repeated, then i would codethis into stored procedures...
1
 
DevildibAuthor Commented:
Any looping that can be done to check for column names matching and then proceeding with appending the column equations?
Let me give u an example:
@sql = 'update test2 set'
loop starts
check for matching column names between two tables
for each match >> append something like test2.col1 = test1.col1 and keep appending it to the sql syntax above
and finally append the where condition or the join on the ID

POSSIBLE?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Possible, yes, but it's going to take a pretty long time to write the custom code to pull that off, and to catch every scenario that would prevent it from  executing successfully without error.
0
 
DevildibAuthor Commented:
but once done, we need not worry for any column additions or deletions made to any of the two tables.Simple column match logic and updations.If it is possible please help me with some sample code for any 2 small tables for a match on ID column
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how would you define the joining/where conditions?
0
 
DevildibAuthor Commented:
Whats the issue with the where condition? Once you are done looping until the last column name matching, you stop there.What you have until at this point is a statement similar to:
update test2 set test2.col1 = test1.col1,test2.col2 = test1.col2..........

 and simply apply the where condition at the end of it
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not clear to me, and i consider myself very good in sql

so please show with some examples how you "see" this where clause. example tables, " before" and "after" data or the sql you want to be build.
the looping will surely and indeed not be a problem
https://www.experts-exchange.com/articles/13640/processing-cursor-vs-temp-table-syntax.html
0
 
DevildibAuthor Commented:
I am sorry for being unclear. I regard myself very poor in SQL. So I am here.
let me provide you my best thoughts in an algorithm good to understand:

function or stored proc starts
@TMdot = 'test1.'
@TDdot = 'test2.'
@s = 'update test2 set '
@MyTableDef --variable to hold test1 column names in loop
@cnt = count of columns in test1 --setting an upperlimit for the loop
For j = 0 To @cnt - 1
 @n = Trim(MyTableDef(j).Name)
  @s = @s ||  TMdot || @n || = || TDdot  || @n  If j < cnt - 1 Then
    s$ = s$ & ","
  Else

  @s = @s ||  from test2,test1
  @s = @s || ' where  TDdot  ||ID = TMdot ||ID  End If
         
next
0
 
DevildibAuthor Commented:
Please consider the above as logic only.Syntax is not good at all
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I regard myself very poor in SQL.
In that case I highly recommend against trying to pull this off, as whatever solution we give you will have to be supported when things change, and any resulting script will be long and complex, therefore I humbly propose that you will not be in a good position to do that.

So ... I HIGHLY ( HIGH - LEE ) recommend explicitly spelling out the columns.
0
 
DevildibAuthor Commented:
@Guy Hengel  : Sir my comment was a reply to your comment.You asked me where do I see the "WHERE" condition going, and I tried to answer.

@Jimbo : Sir, Please have faith.I for got to tell you that I am a fast learner. :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i dont want pseudo sql
i want you to spell out a real example
note that you will need to clarify how to match the columns
as if you think to do the match on the name, the Id column names will likely also match.
so spell out the rules ( in english, not in pseudo sql)

to list columns of a table, use syscolumns system view
you can join thst view to match column names from the two tables
you can insert that into a table variable, and use my above link to loop on that one to apoend to the @sql variable
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so i see the "issue" on how you want to distinguish between those columns that are to be put into the Set, and those for the where...
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now