query a table whos name i pull from other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query a table whos name i pull from other table

Author

Topic

rasher83
Starting Member

24 Posts

Posted - 2008-02-04 : 17:27:12

I have recently moved jobs and have come across an unusual database setup that is not ideal for the resolution of my task at hand.

I need to query table A, which will return 1 and only 1 tablename as a result. there are various tablenames on table A but the query will always return a unique result. (from Table B-G for example)

My problem is now, how do i run my next query on the table i have returned from my first query. I need to be able to reference the value.

Making sense?

Ideal scenario would be if tables B-G were on a single table and had a unique identifier, but this architecture cannot be changed for numerous reasons and a workaround needs to be established. your help is much appreciated,

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-04 : 17:50:19

You will have to build a SQL string in a local variable, and then execute it using the EXECUTE statment or SP_EXECUTESQL.

CODO ERGO SUM

rasher83
Starting Member

24 Posts

Posted - 2008-02-04 : 17:52:21

could you provide some pseudo code as an example, using the pseudonnyms TableA, TableB etc...
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-04 : 17:56:25

quote:
Originally posted by rasher83

could you provide some pseudo code as an example, using the pseudonnyms TableA, TableB etc...
Thanks

You can lookup EXECUTE and SP_EXECUTESQL in SQL Server Books Online.

CODO ERGO SUM

rasher83
Starting Member

24 Posts

Posted - 2008-02-04 : 18:17:30

i have read about these functions and the examples that have been provided do not give examples of this use of the function. it seems to mainly lend itself towards replacing the WHERE part of the queries and not the FROM. any other suggestions?

russell101
Starting Member

3 Posts

Posted - 2008-02-04 : 21:46:25

Hi,

You request is a little hard to understand, but I think this may help you.

If my assumption is correct you pulling the tableName value from a query as a string format, and you want to use that table name to get information from another query. If that is so then try this

DECLARE @TableName varchar(25) --SET this value to the tablename from the result set of your original query
DECLARE @sql varchar(600) -- this will hold the syntax of your new query as a varchar datatype
SET @TableName = 'dbo.tableA' -- the hypothetical table name of your hypothetical result set
SET @sql = 'SELECT * FROM ' +@TableName +'' -- Concatenate the variable table name to your query string
EXEC(@sql) -- execute the statement

Result set should look something similar to this, but relative to whatever your querying
summary_stat_id stat_type_id time_interval
---------------- ------------ -----------------------
0 1 1900-01-01 01:15:00.000

Hope this helps

Thanks Russ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 23:07:13

Another example of case where table name is pulled from a table and then table is queried to retrieve results:-


CREATE TABLE CAT_TABLE_LIST 
(
FIELD_NAME varchar(50),
OPPOSITE_NAME varchar(20),
TBL_NAME varchar(20)
)
INSERT INTO CAT_TABLE_LIST  VALUES ('Height (mm)' , 'COL_1' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Cylinder volume (cm³)' , 'COL_5' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Torgue (daN.m)' , 'COL_7' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Cylinder weight(kg)' , 'COL_13' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Pump Type' , 'COL_9' , 'TBL_13')
INSERT INTO CAT_TABLE_LIST  VALUES ('Fuel Consumption' , 'Col_22' , 'TBL_22')
INSERT INTO CAT_TABLE_LIST  VALUES ('Color' , 'Col_41' , 'TBL_09')



select * from TBL_13
CREATE TABLE TBL_13
(
STOCK_CODE varchar(10),
COL_1 decimal(10,1),
COL_5 int,
COL_7 decimal(10,3),
COL_13 int,
COL_9 varchar(10)
)
INSERT INTO TBL_13 VALUES ('FC-25' , 12.5 , 5 , 15.000 , 45 , 'DP 75')
INSERT INTO TBL_13 VALUES ('FT-45' , 27 , 9 , 40.000 , 35 , 'DP 101')

CREATE TABLE TBL_09
(
STOCK_CODE varchar(10),
COL_41 decimal(10,1),
COL_5 int,
COL_7 decimal(10,3),
COL_13 int,
COL_9 varchar(10)
)
INSERT INTO TBL_09 VALUES ('KC-27' , 10.23 , 5 , 15.000 , 33 , 'KR 168')


CREATE TABLE TBL_22
(
STOCK_CODE varchar(10),
COL_11 decimal(10,1),
COL_15 int,
COL_22 decimal(10,3),
COL_23 int,
COL_19 varchar(10)
)
INSERT INTO TBL_22 VALUES ('KM-29' , 12.5 , 5 , 15.000 , 45 , 'DP 75')
INSERT INTO TBL_22 VALUES ('FC-25' , 123 , 2 , 40.250 , 35 , 'KK 324')

DROP table Results
CREATE TABLE Results --table to get results
(
STOCK_CODE varchar(10),
OPPOSITE_NAME varchar(20),
FIELD_NAME varchar(50),
[VALUE] varchar(50)
)
CREATE PROCEDURE GetValues
 @StockCode varchar(5)

AS

BEGIN
DECLARE @PK varchar(50),@JoinTable varchar(10),@ReqdCol varchar(10),@Sql varchar(8000)


SELECT @PK=MAX(FIELD_NAME)
FROM CAT_TABLE_LIST 


WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable =TBL_NAME,
       @ReqdCol=OPPOSITE_NAME 
FROM CAT_TABLE_LIST 
WHERE FIELD_NAME=@PK
 
SET @Sql='INSERT INTO Results
SELECT j.STOCK_CODE,c.OPPOSITE_NAME,c.FIELD_NAME ,j.' + @ReqdCol + ' FROM  CAT_TABLE_LIST
 c CROSS JOIN ' + @JoinTable +' j WHERE c.OPPOSITE_NAME =''' + @ReqdCol + ''' AND c.TBL_NAME=''' + @JoinTable + ''' AND j.STOCK_CODE=''' + @StockCode +'''' 


EXEC (@Sql)

SELECT @PK = MAX(FIELD_NAME)
FROM CAT_TABLE_LIST 
WHERE FIELD_NAME < @PK
END
RETURN
END
GO

GetValues 'FC-25'
select * from Results

Output
-----------------
STOCK_CODE	OPPOSITE_NAME	FIELD_NAME	VALUE
FC-25	COL_7	Torgue (daN.m)	15.000
FC-25	COL_9	Pump Type	DP 75
FC-25	COL_1	Height (mm)	12.5
FC-25	Col_22	Fuel Consumption	40.250
FC-25	COL_13	Cylinder weight(kg)	45
FC-25	COL_5	Cylinder volume (cm³)	5

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 12:48:36

that might help me but i just want to clarify the problem, and to ask for an actual implimentation of the above with some table names I will provide.

on TableA i have the following data:

JobID tablename otherdata
------------------------------------------------
123 TableG xyz
456 TableC iop
789 TableM rty

I do a search on TableA by the JobID and return the JobID column only.
I now want to search the table mentioned in my result - eg TableG for whatever data it contains. Does visakh16's solution tie in with what i want?

If so could someone alter it to fit my requirements
thanks to all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 12:53:09

do all these tables(TableG,TableC,..) have same structure?If not then whats is output you expect? Just return all columns?

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 12:56:24

Yes, they all have the exact same structure. I will initally require all columns back as output, but as they are identical I should have no problem in selecting specific columns at a later date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:13:46

Try this:-

DECLARE @PK varchar(50),@JoinTable varchar(10),@Sql varchar(8000)

SELECT @PK=MIN(JobID)
FROM TableA


WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable =TBL_NAME
FROM TableA
WHERE JobID=@PK
 
SET @Sql='SELECT '+ @PK +',* FROM '+ @JoinTable
EXEC (@Sql)

SELECT @PK=MIN(JobID)
FROM TableA 
WHERE JobID < @PK
END

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 13:24:34

i am getting the following error:
SQL Server Database Error: Must declare the scalar variable "@PK". SELECT @PK=MIN(JobID)

with reference to the 4th last line.

any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:30:45

[code]DECLARE @PK int,@JoinTable varchar(10),@Sql varchar(8000)

SELECT @PK=MIN(JobID)
FROM TableA

WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable =TBL_NAME
FROM TableA
WHERE JobID=@PK

SET @Sql='SELECT '+ @PK +',* FROM '+ @JoinTable
EXEC (@Sql)

SELECT @PK=MIN(JobID)
FROM TableA
WHERE JobID < @PK
END[/code]

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 13:38:26

ok, i've had to extend the jointable length to accomadate the tablename length, now i have the following error:

Invalid column name 'v2F4D95503A664A298A362061DFA625C8'.

which is one of my table names (TableG)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:39:43

Can you post actual query used?

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 13:41:18

DECLARE @PK varchar(50),@JoinTable varchar(40),@Sql varchar(8000)

SELECT @PK=MIN(JobID)
FROM Config_RCP

WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable = JobID
FROM Config_RCP
WHERE JobID=@PK

SET @Sql='SELECT '+ @PK +',* FROM '+ @JoinTable
EXEC (@Sql)

SELECT @PK=MIN(JobID)
FROM Config_RCP
WHERE JobID < @PK
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:45:03

quote:
Originally posted by rasher83

DECLARE @PK int,@JoinTable varchar(40),@Sql varchar(8000)

SELECT @PK=MIN(JobID)
FROM Config_RCP

WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable = <your tablename field here>FROM Config_RCP
WHERE JobID=@PK

SET @Sql='SELECT '+ @PK +',* FROM '+ @JoinTable
EXEC (@Sql)

SELECT @PK=MIN(JobID)
FROM Config_RCP
WHERE JobID > @PK
END

change like this

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 13:57:10

sorry visakh, i mislabled my columns previously,

the column name that references the tablenames on Config_RCP is actually JobID.
so what i posted last does have the correct tablename field in that position.

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 16:00:54

can you tell me which line referrs to searching of our result table (whichever one we get from our query of TableA or Config_RCP)?

rasher83
Starting Member

24 Posts

Posted - 2008-02-06 : 19:39:11

i got it!!!!!

slight modification to what was suggested, the column name was not in the second table, just other column names so what worked for me was:

DECLARE @PK varchar(50),@JoinTable varchar(40),@Sql varchar(8000)

SELECT @PK=MIN(JobID)
FROM Config_RCP

WHILE @PK IS NOT NULL
BEGIN
SELECT @JoinTable = JobID
FROM Config_RCP
WHERE JobID=@PK

SET @Sql='SELECT * FROM '+ @JoinTable
EXEC (@Sql)

SELECT @PK=MIN(JobID)
FROM Config_RCP
WHERE JobID < @PK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources