UPDATING 230 COLUMNS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATING 230 COLUMNS

Author

Topic

Freddie
Starting Member

29 Posts

Posted - 2006-10-31 : 12:59:49

Hello All:

I would like to know what is the best way to update 230 columns from one table to the other. Do i need to define all these variables in my proc??

thx in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 13:03:25

yes you have to specify them all.


update t1
set col1 = t2.col1,
    col2 = t2.col2,
    ...
    colN = t2.colN
from table1 t1 join table2 t2 on t1.id = t2.id

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 13:29:54

Or INSERT the new row from other table, delete old row and update pk.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 13:32:21

what does he gain by that?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 13:42:49

Only time spent writing the query...

INSERT Target SELECT * FROM Source
DELETE Target WHERE OldRow = <somevalue>
UPDATE Target SET NewRow = OldRowID

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 14:01:24

select * ?? do my eyes deceive me?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources