Charles,

SELECT INTO #temp creates excessive locking on your tempdb. CREATE TABLE #temp followed by a INSERT...SELECT reduces the locking.

But depending on the horsepower of your server a 12 table join is not necessarily excessive. Look at the query plan to see the effectiveness of different ways of doing the queries. If you see table scans on large tables then you have things that need better optimization.

>I have a stored procedure that is performing a lookup function that ends up accessing way too many tables ... 12 lets say for example (I normally like to keep a query to six tables or less).
>
>In VFP I'd handle this buy breaking up the query into several different queries that place intermediate results into cursors. Then, I'd use the cursors to bring in the other resultant sets from the tables by joining cursors (that usually have quite small resultant sets) with the additional tables from which I need data.
>
>This can be done with SQL Server using temporary tables and the # sign.
>
>SELECT tb1.fl1 INTO #tTemp FROM tb1
>
>My question is this: with SQL Server is this a best practice? Or does this slow SQL Server down by balling up the execution plan? How do people optimize a lookup query that has an ungodly number of tables?