Appending strings in query to create table name?

[MGM]

Hello everyone,

I once again have a problem :p I need to write a query to get the total amount of rows in a table. Problem is, the table name is dynamic and part of its value is stored in another table. Here's my query:

SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM CDT_DEM_Pages AS a
ORDER BY name

What I'm trying to do is to count the total amount of rows in a table named CDT_DEM_DATA_[blank] where [blank] is a name like "test" that is stored in CDT_DEM_Pages as a column (named alphanumericnam e). I'm not exactly sure how to append the alphanumericnam e column's data into the query dynamically to find the total amount of rows in it...

MGM out

[ck9663]

Hello everyone,

I once again have a problem :p I need to write a query to get the total amount of rows in a table. Problem is, the table name is dynamic and part of its value is stored in another table. Here's my query:

SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM CDT_DEM_Pages AS a
ORDER BY name

What I'm trying to do is to count the total amount of rows in a table named CDT_DEM_DATA_[blank] where [blank] is a name like "test" that is stored in CDT_DEM_Pages as a column (named alphanumericnam e). I'm not exactly sure how to append the alphanumericnam e column's data into the query dynamically to find the total amount of rows in it...

MGM out

use dynamic query:

declare @mytablename varchar(50)
 
set @mytablename = 'CDT_DEM_DATA' + '_Pages'
 
exec ('SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM ' + @mytablename + '  AS a
ORDER BY name')
 
set @mytablename = 'CDT_DEM_DATA' + '_NoPages'
 
exec ('SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM ' + @mytablename + '  AS a
ORDER BY name')
 

The values '_Pages' and '_NoPages' may be a variable.


You may also place the entire query in a variable

declare @strquery varchar(50)
 
set @strquery = 'SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM ' + @SomeVariableForYourTableName + '  AS a
ORDER BY name'
 
exec (@strquery)
 
 

Happy Coding...

-- CK

[MGM]

use dynamic query:

declare @mytablename varchar(50)
 
set @mytablename = 'CDT_DEM_DATA' + '_Pages'
 
exec ('SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM ' + @mytablename + '  AS a
ORDER BY name')
 
set @mytablename = 'CDT_DEM_DATA' + '_NoPages'
 
exec ('SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM ' + @mytablename + '  AS a
ORDER BY name')
 

The values '_Pages' and '_NoPages' may be a variable.


You may also place the entire query in a variable

declare @strquery varchar(50)
 
set @strquery = 'SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM ' + @SomeVariableForYourTableName + '  AS a
ORDER BY name'
 
exec (@strquery)
 
 

Happy Coding...

-- CK

Well the problem with that is that the text that I need to append is not static and is actually a row inside another table (CDT_DEM_Pages) . That is to say, CDT_DEM_Pages has a column named alphanumericnam e that has text in it, this text need's to be appended to the end of [CDT_DEM_DATA_] so that I can get the data I need. The bold part in the query below is what needs to be changed:

SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM CDT_DEM_Pages AS a
ORDER BY name

MGM out

[ck9663]

Well the problem with that is that the text that I need to append is not static and is actually a row inside another table (CDT_DEM_Pages) . That is to say, CDT_DEM_Pages has a column named alphanumericnam e that has text in it, this text need's to be appended to the end of [CDT_DEM_DATA_] so that I can get the data I need. The bold part in the query below is what needs to be changed:

SELECT id, name,
      (SELECT   COUNT(*) AS amtColumns
      FROM      CDT_DEM_Columns
      WHERE     (pageid = a.id)) AS amtColumns,
      (SELECT   COUNT(*) AS amtRows
      FROM      CDT_DEM_DATA_+a.alphanumericname
      WHERE     (pageid = a.id)) AS amtRows
FROM CDT_DEM_Pages AS a
ORDER BY name

MGM out

I would need:
1. The datatype of a.id
2. The actual fieldname of a.alphanumericn ame

-- CK

[MGM]

I would need:
1. The datatype of a.id
2. The actual fieldname of a.alphanumericn ame

-- CK

a.id is an integer and the identity column for a (CDT_DEM_Pages)
a.alphanumeric is an nvarchar(50) that is NOT NULL

MGM out