Running Same query in tables 001, 002, 003, 004 ........, 085 2

[DerickD]

Hi All,

I have about 85 tables that need to have the same query run on them.

I can set up each query manualy but, this does take alot of time and if something is wrong.......

The Query is :

Update dbo.Customer0001 SET REF =
CASE LEFT(ORDER, 7)
WHEN '123123' THEN 23 + RIGHT(ORDER,2)
WHEN '123185' THEN 24 + RIGHT(ORDER,2)
END
WHERE NAME = 'Harvard'

So I need to run the same query on tables Customer0002, Customer0003, Customer0002, Customer0004 ......... Customer0085 ...etc....

I thought about having a loop running in 'Local Packages' but have no idea how to set up the loop.

Thanks for reading this and looking foward to awarding many stars for your help....

 

[JayKusch]

Could be more eloquent but it does the trick!


declare @Command VarChar(400)
DECLARE @FullCustID VarChar(12)
declare @NextID Int

set @Command = ''
set @FullCustID = ''
set @NextID = 1


--WHILE @NextID < 10000

IF @NextID < 10

SET @FullCustID = 'Customer000' + Convert(Char,@NextID)


IF @NextID > 9
AND @NextID < 100

SET @FullCustID = 'Customer00' + Convert(Char,@NextID)


IF @NextID > 99
AND @NextID < 1000

SET @FullCustID = 'Customer0' + Convert(Char,@NextID)

IF @NextID > 999
AND @NextID < 10000

SET @FullCustID = 'Customer' + Convert(Char,@NextID)


SET @Command =

'Update dbo.' + @FullCustID +
' SET REF = CASE LEFT(ORDER, 7)
WHEN ''' + '123123' + ''' THEN 23 + RIGHT(ORDER,2)
WHEN ''' + '123185' + ''' THEN 24 + RIGHT(ORDER,2)
END
WHERE NAME = ' + '''' + 'Harvard' + ''' '

PRINT @Command

EXEC (@Command)

SET @NextID = NextID + 1


END



Thanks

J. Kusch

 

[ClaireHCM]

set quoted_identifier off
declare @counter int
set @counter = 1
while @counter < = 85
begin
declare @table varchar(12)
set @table = 'Customer'+right('000'+convert(varchar(2),@counter),3)
exec (&quot;Update dbo.&quot;+@table+&quot; SET REF = CASE LEFT(ORDER, 7) WHEN '123123' THEN 23 + RIGHT(ORDER,2)WHEN '123185' THEN 24 + RIGHT(ORDER,2) END WHERE NAME = 'Harvard'&quot
set @counter = @counter +1
end

 

[JayKusch]

Use Claire's - Mine bombed!

Thanks

J. Kusch

 

[SQLBill]

Why not use the undocumented procedure sp_MSforeachtable?

Procedure:
Sp_MSforeachtable
@command1
@replacechar = '?'
[,@command2]
[,@command3]
[,@whereand]
[,@precommand]
[,@postcommand]

Purpose:
Execute up to three commands for every table in a database (optionally matching the @whereand clause) @replacechar will be replaced with the name of each table. @precommand and @postcommand can be used to direct commands to a single result set.

Example:
EXEC sp_MSforeachtable @command1 = 'PRINT &quot;Listing ?=', @command2='SELECT * FROM ?', @whereand=' AND name like &quot;title%&quot;'


The above information is from Guru's Guide to Transact-SQL by Ken Henderson

-SQLBill

 

[DerickD]

HI,

Thanks for the replys,

the procedure suggested by ClaireHsu works a treat!

One realy stupid question. If I want to scheduale this proc. where do I set it up?

Thanks
DerickD

 

[hspoulsen]

Hi Derick,

On one of my database, I've a table for each calendar year and month. As in Order2003_09, Order2003_08 etc.

I've set up a view that spans all these tables.
It goes
create view myview
as
select * from Order2003_09
UNION ALL
select * from Order2003_08
go

When a new table is created, my view is recreated as well.
I have a stored procedure for this job.

When I want to query/update/delete across a time period, I just use the view. I also have a view that points to the latest/newest table.

It is running so fast !
And the ASP code is so simple.
My client was very concerned about the speed, and they could not believe elegant this solution was, compared to their own code.

HIH
Henrik Staun Poulsen
Stovi Software

 

[DerickD]

Hi Henrik,

That sounds like a great idea, creating a view that contains a list of all the tables. I assume then I could modify the code suggested by ClaireHsu to take the view as the source for the table names.

(When I say : &quot;I assume then I could&quot;, I really mean &quot;How would you&quot

Thanks for the suggestion.
Derick.

 

[hspoulsen]

Derick,

How?
Just assemble your SQL string with the name of the view instead of the table name.

As in
Select * from CustomerAll

where CustomerAll is the name of the view.
or to use your example

Update dbo.CustomerAll SET REF =
CASE LEFT(ORDER, 7)
WHEN '123123' THEN 23 + RIGHT(ORDER,2)
WHEN '123185' THEN 24 + RIGHT(ORDER,2)
END
WHERE NAME = 'Harvard'

Regards
Henrik

 

[DerickD]

Amazing!!