sql - How to update one table from another one without specifying column names? -

i have 2 tables identical structure , large number of fields (about 1000). need perform 2 operations 1) insert second table rows fist. example:

insert [1607348182] select *  _tmp_1607348182; 

2) update first table second table update can't found proper sql syntax update.

queries like:

update [1607348182] set [1607348182].* = tmp.* [1607348182] inner join _tmp_1607348182 tmp on tmp.recordid = [1607348182].recordid 

or

update [1607348182] [1607348182] inner join _tmp_1607348182 tmp on tmp.recordid = [1607348182].recordid 

are invalid.

not sure if you'll able accomplish without using dynamic sql build out update statement in variable.

this statement return list of columns based on table name put in:

select name syscolumns [id] = (select [id] sysobjects name = 'tablename') 

not sure if can avoid loop here....you'll need load results above cursor , build query it. psuedo coded:

set @query = 'update [1607348182] set ' load cursor --(we use @name hold column name) while stillrecordsincursor set @query = @query + @name + ' = tmp_[1607348182]. ' +@name + ',' load next value cursor loop! 

when query done being built in loop, use exec sp_executesql @query.

just little warning...building dynamic sql in loop can bit confusing. trouble shooting, putting select @query in loop , watch @query built.

edit: not sure if you'll able 1000 rows in update @ once...there logical limits (varchar(8000)?) on size @query can grow too. may have divide code handles 50 columns @ time. put columns syscolumns select statement temp table id , build dynamic sql updates 20 columns (or 50?) @ time.

another alternative use excel mass build this. column select , copy results column of spreadsheet. put '= in column b, tmp.[12331312] in column c, copy column column d, , comma column e. copy entire spreadsheet notepad, , should have columns of update statement built out you. not bad solution if 1 shot event, not sure if i'd rely on on-going solution.