|
Subject:
|
Challenging Problem in simple SELECT
|
|
Posted By:
|
software_developer_kk
|
Post Date:
|
12/26/2005 9:46:53 AM
|
Dear Readers,
I want to select data from a table name which is a result of a query .
Basically ,
a sql query is giving a table name based on some conditions & i want to fetch data from that table name .
waiting for a good reply
Cheers :
K K Singh.
+91-9810481159
Why to be GOOD when U have the ability to be GREAT
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/27/2005 7:27:39 AM
|
I don't understand. You are creating a temporary table with a dynamic table name, and the dynamic table name is based on conditions in the query?
I would do one of two things:
1. use a standard temp table name based on the name of the query. Perhaps purge this table before you run the query using a delete query to make sure the data is clean and complete on each run of your initial query.
2. OR use the same conditions used in the first query to determine the table name when you run the second select query. This will give you the name of the temp table if it is dynamic.
Did that help?
mmcdonal
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
12/27/2005 11:32:09 AM
|
software_developer_kk mentioned nothing about temporary tables.
I recommend that you use dynamic SQL
Example:
DECLARE @tname sysname
SELECT TOP 1 @tname = table_name FROM Information_Schema.Tables
DECLARE @SqlString varchar(8000)
SELECT @SqlString = 'SELECT * FROM [' + @tname + ']'
EXEC (@SqlString )
-- Generally you should avoid Select * but in this case you may not know the column names
-- Generally you should avoid Select without where but a where is hard to do when you may not know the column names
-- Generally sp_ExecuteSQL is better than EXEC but for this example it does not matter, but if pass in parameters then it is recommended to use sp_executesql
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
|
|
Reply By:
|
Anantsharma
|
Reply Date:
|
1/6/2006 4:49:54 AM
|
Exxxxxxacltyyyy...david is right ;-))
B. Anant
|
