Run query based on values in another table

**Erland Sommarskog** · Mar 29 '07, 07:25 AM

Re: Run query based on values in another table

bubbles (bubbles.one@ho tmail.com) writes:

Newbie transiting from VBA to TSQL, using SQL Server 2005 Enterprise:
>
Need help to do this:
>
Open Table_A
>
WITH TableA
DO UNTIL .EOF
Read value from TableA.ColumnA
Run SQL Statement on TableB based on value
Move to the next record
LOOP
END
>
>
How do I do this in TSQL?

It is not unlikely that this is a single SQL statement, but it depends on
what operation you intend do to on TableB. Assuming that the operation
is "Give me the total order sum for each customer", the query in Northwind
is:

SELECT C.CompanyName, SUM(OD.Quantity * OD.UnitPrice)
FROM [Order Details] OD
JOIN Orders O ON OD.OrderID = O.OrderID
JOIN Customers C ON O.CustomerID = C.CustomerID
GROUP BY C.CompanyName

A very important lesson of this is that the mindset in SQL is completely
different from a client language.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**bubbles** · Mar 29 '07, 08:55 AM

Re: Run query based on values in another table

Thanks for the reply.

What I need to do is this:

Every night I pull data from a likned server. This is an automated
job.

>From this data pull, I'll generate a list of products into a table

(TableA).

TableB contains millions of records with the details of transactions
on the products.

I want to loop through TableA's values (product names) to generate and
run a series of
SQL statements with these value so I can generate various analyses.

Thanks,
Bubbles

On Mar 29, 3:22 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

>
It is not unlikely that this is a single SQL statement, but it depends on
what operation you intend do to on TableB. Assuming that the operation
is "Give me the total order sum for each customer", the query in Northwind
is:
>
SELECT C.CompanyName, SUM(OD.Quantity * OD.UnitPrice)
FROM [Order Details] OD
JOIN Orders O ON OD.OrderID = O.OrderID
JOIN Customers C ON O.CustomerID = C.CustomerID
GROUP BY C.CompanyName
>
A very important lesson of this is that the mindset in SQL is completely
different from a client language.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx- Hide quoted text -
>
- Show quoted text -

**Erland Sommarskog** · Mar 29 '07, 12:15 PM

Re: Run query based on values in another table

bubbles (bubbles.one@ho tmail.com) writes:

What I need to do is this:
>
Every night I pull data from a likned server. This is an automated
job.
>

>>From this data pull, I'll generate a list of products into a table

(TableA).
>
TableB contains millions of records with the details of transactions
on the products.
>
I want to loop through TableA's values (product names) to generate and
run a series of
SQL statements with these value so I can generate various analyses.

First lesson in SQL: stop think in loops. The normal approach is that you
handle all data at once as in the query that I showed you. There are
exceptions when looping may be called for, or may be the best solution,
but nothing you have said this far, indicates that this would be necessary.

Since I don't know what the queries against TableB look like, I cannot
assist more than with the example I provided. I forgot to mention that
it runs in the Northwind database. (Which does not ship with SQL 2005,
but is on SQL 2000, and can be downloaded from Microsoft.com.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**bubbles** · Mar 30 '07, 01:05 AM

Re: Run query based on values in another table

On Mar 29, 8:10 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

>
First lesson in SQL: stop think in loops. The normal approach is that you
handle all data at once as in the query that I showed you. There are
exceptions when looping may be called for, or may be the best solution,
but nothing you have said this far, indicates that this would be necessary.
>
Since I don't know what the queries against TableB look like, I cannot
assist more than with the example I provided. I forgot to mention that
it runs in the Northwind database. (Which does not ship with SQL 2005,
but is on SQL 2000, and can be downloaded from Microsoft.com.)
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

Thank you for your patience.
Here is the whole deal:

01. Download tblWarrantyCall s, tblDeliveries, tblReplacements

02. Append tblReplacements to tblDeliveries

03. Merge tblWarrantyCall s, tblDeliveries into tblCalls (based on
certain criteria)

04. Generate a product list tblProducts from tblCalls

05. Using the product list, run queries against tblCalls such that
the following analyses are generated into different tables
(tables created on the fly).

Each product will cause about 20 tables to be created (deleted
and
re-created at each run of the job) with various analyses.

These tables are then linked to front-end Microsoft Access
applications
that present the data both graphically and in the raw (if
required).

This process saves users a lot of time, as they will not need to
run queries
against a humongous database. All required analyses are already
pre-generated.
Even raw data for each product are seperated into their own
tables, so running
queries against them are much faster.

Question:
1. Is this a good strategy?
2. How to achieve this in TSQL?

Thanks!
Bubbles

**bubbles** · Mar 30 '07, 06:05 AM

Re: Run query based on values in another table

Solved this problem using cursors.
Thanks!

On Mar 29, 8:10 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

>
First lesson in SQL: stop think in loops. The normal approach is that you
handle all data at once as in the query that I showed you. There are
exceptions when looping may be called for, or may be the best solution,
but nothing you have said this far, indicates that this would be necessary.
>
Since I don't know what the queries against TableB look like, I cannot
assist more than with the example I provided. I forgot to mention that
it runs in the Northwind database. (Which does not ship with SQL 2005,
but is on SQL 2000, and can be downloaded from Microsoft.com.)
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

**Ed Murphy** · Mar 30 '07, 02:45 PM

Re: Run query based on values in another table

bubbles wrote:

Solved this problem using cursors.

Which works, but is slower and more complex than set-based
operations. Sometimes it's necessary (e.g. sending e-mails,
generating PDFs); more often it isn't. In your particular
case, "running [Access] queries against a humongous database"
shouldn't be a problem if you use proper indexes.

**Erland Sommarskog** · Mar 30 '07, 09:35 PM

Re: Run query based on values in another table

bubbles (bubbles.one@ho tmail.com) writes:

05. Using the product list, run queries against tblCalls such that
the following analyses are generated into different tables
(tables created on the fly).
>
Each product will cause about 20 tables to be created (deleted
and re-created at each run of the job) with various analyses.
>
Question:
1. Is this a good strategy?
2. How to achieve this in TSQL?

If the table indeed have different layout depending on product, you
would indeed have to loop, for instance using a cursor as you discovered.
But if all sets of tables have the same layout, you should probably have
one single set of tables instead, and in this case there would not be any
need for loops.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**bubbles** · Apr 3 '07, 01:05 AM

Re: Run query based on values in another table

Thank you for your reply.

The tables are in fact seperated by products, as each set of product-
specific data are handled by
different engineers. These sets of data need to be sliced and diced
into various analyses, and the
engineers would also need to dive into raw data for some of their
work.

Because these product-specific data sets are large (several million
records each), I thought that
perprocessing and seperating them into their respective tables would
help the engineers access
their analyses and data faster.

In any case, I have already done the needful and have had the required
tables generated.

Thanks!
Bubbles

On Mar 31, 5:33 am, Erland Sommarskog <esq...@sommars kog.sewrote:

>
If the table indeed have different layout depending on product, you
would indeed have to loop, for instance using a cursor as you discovered.
But if all sets of tables have the same layout, you should probably have
one single set of tables instead, and in this case there would not be any
need for loops.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

**Ed Murphy** · Apr 3 '07, 05:15 AM

Re: Run query based on values in another table

bubbles wrote:

The tables are in fact seperated by products, as each set of product-
specific data are handled by
different engineers. These sets of data need to be sliced and diced
into various analyses, and the
engineers would also need to dive into raw data for some of their
work.
>
Because these product-specific data sets are large (several million
records each), I thought that
perprocessing and seperating them into their respective tables would
help the engineers access
their analyses and data faster.

This would probably still be better handled using a single table,
including the separating column(s) in indexes as appropriate - though I
understand the limited motivation for applying further development
effort to a system that does work.

You could save the engineers the trouble of remembering an extra WHERE
clause by providing each of them with a set of views, along the lines of

create view TheTable_eng1 as
select *
from TheTable
where some_column = 'eng1'

Run query based on values in another table

Run query based on values in another table

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment