Appending strings in query to create table name? | Newbie | | Join Date: Aug 2007
Posts: 19
| |
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
|  | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: Appending strings in query to create table name?
Quote:
Originally Posted by 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
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
| | Newbie | | Join Date: Aug 2007
Posts: 19
| | | re: Appending strings in query to create table name?
Quote:
Originally Posted by ck9663 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
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: Appending strings in query to create table name?
Quote:
Originally Posted by MGM 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
| | Newbie | | Join Date: Aug 2007
Posts: 19
| | | re: Appending strings in query to create table name?
Quote:
Originally Posted by ck9663 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
|  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,327 network members.
|
