Loop through & Update multiple dbs 1

[avu]

Hi, someone help please:

I have 3 tables: "Jan_2004", "Feb_2004", "Mar_2004"
They all have similar fields:
'orderId',
'OrderCode',
'Department',
'Language',
'PartOfWorld'

And 2 other tables:
"OrderLookup" : 'OrderCode', 'Dept'
"LangLookup" : 'Language', 'Country'

What I want to do is
1. Loop through all tbls ending with '_2004'
2. In each tbl, use 'OrderCode', lookup using 'OrderLookup.OrderCode', return 'OrderLookup.Dept', and update to 'Department'
3. Same thing with 'Language', lookup 'LangLookup.Language', return 'LangLookup.Country', and update to 'PartOfWorld'

I can use cursor in a stored procedure without problem, but I'm stuck at steps 2 & 3. My problem is to loop through the recordset in each '_2004' tbl & do the update.

Any help would be appreciated.

 

[nigelrivett]

>> I have 3 tables: "Jan_2004", "Feb_2004", "Mar_2004"
Oh dear.

something like

create table #a (tbl varchar(128))
insert #a select name from sysobjects where name like '%2004%' and xtype = 'U'
declare @tbl varchar(128), @sql varchar(2000)
select @tbl = ''
while @tbl < (select max(tbl) from #a)
begin
select @tbl = min(tbl) from #a where tbl > @tbl
select @sql = 'update ' + @tbl + ' set Department = OrderLookup.Dept from OrderLookup where OrderLookup.OrderCode = ' + @tbl + '.OrderCode'
exec (@sql)
end

and do the same for the other fields.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[avu]

Thanks nigelrivett for your quick response. But I've got this error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Jan'.

and it repeats for 'Feb' & 'Mar' tables.

Any reason why?

 

[nigelrivett]

change
exec (@sql)
to
select @sql

and see what is being executed.
also see what is in #a before the loop.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[avu]

Thanks again nigelrivett.

Your response absolutely helped me solve the problem. It turned out that a table "6_2004" was added without my knowledge, so there was an issue with tables starting with a number. Changing it to "Jun_2004" sure fixed the problem.

Cheers.