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 alphanumericname). I'm not exactly sure how to append the alphanumericname 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 alphanumericname). I'm not exactly sure how to append the alphanumericname 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 alphanumericname 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 alphanumericname 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.alphanumericname

-- CK

[MGM]

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

-- 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