Updating Multiple Tables

[Erics44]

Hi I have a simple update statement and I would like to apply it to multiple tables

I am not sure how to do this without changing the table name each time

here is my sql so far, the @market part is the part that will vary with the tables

"
Update tblTimetable_@MarketID
set Resp='Client'
where resp='Factory' or resp ='MC' or Resp='Market'
"

 

[SonOfEmidec1100]

You need [blue]Dynamic SQL[/blue] many threads.

For example from July 2003 thread183-596004

 

[Erics44]

Thanks very much

this should be very useful

 

[JohnDTampaBay]

Check out the procedure sp_MSForEachTable in the master database. If you can provide the appropriate @whereand parameter to limit the table list from sysobjects, you should be able to use the procedure. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Erics44]

Hi
I have a problem now with my stored proc

here is my proc

"
alter Procedure TableSelect
@TableName VarChar(100),
@Month nvarchar(7)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT Market, Activity FROM '
SELECT @SQL = @SQL + @TableName + ' WHERE [Month]='
SELECT @sql = @sql + @Month
SELECT @sql = @sql + ' Group by Market, activity '

Exec ( @SQL)

GO
"

This is where I am trying it out

"
exec dbo.GenericTableSelect 'tblTimetable_0116', '09/2004'
"

This is the error I am getting


"
Syntax error converting the nvarchar value '10/2004' to a column of data type int.
"

I do have my data types correct, the entries in the Month field are either 09/2004 or 10/2004 and are nvarchar(7)

any ideas please?

 

[Erics44]

sorry ignore that last post, it was a bit stoopid