Pass Table Name by joining Constant String and A Field Value from Same Row

Question

Pass Table Name by joining Constant String and A Field Value from Same Row

sam.cool.just4u

SSC Veteran

Points: 211
More actions
November 30, 2018 at 3:16 am

#382379

I have two tables in this query.
SELECT [pID],[pName],(SELECT COUNT([tableProfileID]) AS COUNT_RECORDS FROM ' + 'profileTable_' + profileMaster.pID) As Records FROM [profileMaster]
Where 'profileTable_' is constant in Table Name e.g., 'profileTable_x' where x is value from same row from field pID
How can we achieve this ?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Only with Dynamic SQL. This, however, has the smell of bad data design. It might be better to take a step back and show us the set up you have, and we can then explain how to improve it to a more normalised method.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 2

Thanks for the Reply. Its a VB.NET Project.
Actually this Table is Dynamically Created with each Profile Creation for storing data of that Profile.
Table Name, Field Names, No of Columns all are dynamic. They are only know while creating the Profile based on User Need.
Its needed to see how many records in that table. If its 0 it is allowed to delete. Hence I need to populate number of records against that Profile.

Thom A SSC Guru Points: 99126 More actions · Answer 3

So wait, the columns are bespoke per table?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 4

Sorry I didn't understand your question.
I have a profileMaster Table; with pID identity column

So when saving; A Record is Created in profileMaster.
Then from the identity of that column (pID) lets say if its 1 then, a Table is created named profileTable_1
Next record created pID=2 in profileMaster; one more Table is created named profileTable_2

Now, what I am doing in below query is getting Number of records count by passing Table name of that Profile.
SELECT [[pIDpID],[],[pNamepName],(],(SELECT COUNTSELECT COUNT([([tableProfileIDtableProfileID])]) AS COUNT_RECORDS FROM AS COUNT_RECORDS FROM ' + '' + 'profileTable_profileTable_' + profileMaster.pID) As Records FROM [profileMaster]' + profileMaster.pID) As Records FROM [profileMaster]

Thom A SSC Guru Points: 99126 More actions · Answer 5

Then all the tables have the same definition? Then this design needs to ideally change. Have a Profile table which has the ID of the Profile; not 1 Profile table for every Profile. So, instead of:

CREATE TABLE dbo.Profile (ProfileID int, SomeValue varchar(10));
INSERT INTO dbo.Profile(ProfileID, SomeValue)
VALUES(1,'asdh');
CREATE TABLE dbo.ProfileData1 (SomeOtherValue varchar(10));
INSERT INTO dbo.Profile(ProfileID, SomeValue)
VALUES(2,'asdh');
CREATE TABLE dbo.ProfileData2 (SomeOtherValue varchar(10));

Just have a single ProfileData table (as well as the Profile Table):

CREATE TABLE dbo.ProfileData (ProfileDataID int,
 ProfileID int,
 SomeOtherValue varchar(10))

Then the query you have becomes as simple as:

SELECT [ProfileID],
    [SomeValue],
    COUNT(PD.ProfileDataID) AS Records
FROM dbo.Profile P
   LEFT JOIN dbo.ProfileData PD ON P.ProfileID = PD.ProfileID
GROUP BY [ProfileID],
    [SomeValue];

I can't see why you need separate tables. Like I said, it's just bad design.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 6

Thanks for the response. Sorry this won't work in my scenario.
As the Number of Columns Change, field Types change in every ProfileDataX table created.
ProfileData1 may have 2 column with varchar & int columns
and ProfileData2 may have 3 columns int, bit and varchar.
Hope its clear now.

Thom A SSC Guru Points: 99126 More actions · Answer 7

Then your only method is Dynamic SQL. With a large amount of data, this WILL perform poorly, as you are having to refer to the table dbo.Profile for every Profile. Like I said, the better option is to fix your data set up.

USE Sandbox;
CREATE TABLE dbo.[Profile] (ProfileID int, SomeValue varchar(10));
INSERT INTO dbo.[Profile](ProfileID, SomeValue)
VALUES(1,'asdh');
CREATE TABLE dbo.ProfileData1 (SomeOtherValue varchar(10));
INSERT INTO dbo.ProfileData1 (SomeOtherValue)
VALUES('asdkajhsd'),
   ('asdklasb');
INSERT INTO dbo.[Profile](ProfileID, SomeValue)
VALUES(2,'asdh');
CREATE TABLE dbo.ProfileData2 (SomeOtherValue varchar(10));
--Leaving this one empty
GO
DECLARE @SQL nvarchar(MAX);
SET @SQL = STUFF((SELECT NCHAR(10) +
          N'UNION ALL' + NCHAR(10) +
          N'SELECT P.ProfileID,' + NCHAR(10) +
          N'    P.SomeValue,' + NCHAR(10) +
          N'   COUNT(PD.[Row]) AS Records' + NCHAR(10) +
          N'FROM dbo.[Profile] P' + NCHAR(10) +
          N'  OUTER APPLY(SELECT 1 AS [Row] FROM dbo.' + QUOTENAME(N'ProfileData' + CONVERT(varchar(10),P.ProfileID)) + N') PD' + NCHAR(10) +
          N'WHERE P.ProfileID = ' + QUOTENAME(P.ProfileID,N'''') + NCHAR(10) +
          N'GROUP BY P.ProfileID,' + NCHAR(10) +
          N'    P.SomeValue'
       FROM dbo.[Profile] P
       ORDER BY P.ProfileID ASC
       FOR XML PATH(N'')),1,11,N'') + N';';
PRINT @SQL;
EXEC sys.sp_executesql @SQL;
GO
DROP TABLE dbo.ProfileData2;
DROP TABLE dbo.ProfileData1;
DROP TABLE dbo.[Profile];

If you need to debug the code, PRINT is your best friend (or SELECT, if more than 4,000 characters).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 8

Thanks a Lot. Looking into it how to put across in my need. Looks like it will serve the purpose.
Also this table will hardly have max to max not more than 100 records. So no issue regarding the Performance.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

sam.cool.just4u - Friday, November 30, 2018 4:19 AM
Thanks for the response. Sorry this won't work in my scenario.
As the Number of Columns Change, field Types change in every ProfileDataX table created.
ProfileData1 may have 2 column with varchar & int columns
and ProfileData2 may have 3 columns int, bit and varchar.
Hope its clear now.

Have you considered using XML or JSON for this instead of a multiple tables approach?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Thom A SSC Guru Points: 99126 More actions · Answer 10

ChrisM@Work - Friday, November 30, 2018 6:12 AM
sam.cool.just4u - Friday, November 30, 2018 4:19 AM
Thanks for the response. Sorry this won't work in my scenario.
As the Number of Columns Change, field Types change in every ProfileDataX table created.
ProfileData1 may have 2 column with varchar & int columns
and ProfileData2 may have 3 columns int, bit and varchar.
Hope its clear now.
Have you considered using XML or JSON for this instead of a multiple tables approach?

That would be a much better scalable solution to what the OP has now.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 11

ChrisM@Work - Friday, November 30, 2018 6:12 AM
sam.cool.just4u - Friday, November 30, 2018 4:19 AM
Thanks for the response. Sorry this won't work in my scenario.
As the Number of Columns Change, field Types change in every ProfileDataX table created.
ProfileData1 may have 2 column with varchar & int columns
and ProfileData2 may have 3 columns int, bit and varchar.
Hope its clear now.
Have you considered using XML or JSON for this instead of a multiple tables approach?

hmm interesting never worked with either. can you please elaborate how it can be used and searched for records like we do in sql tables.
Also I will have a corresponding filestore table against each profile table which will store the documents for this profile. If its easier and more manageable than this I can change it to either json or xml as I am still at initial level of project. Your kind guidance is highly appreciated.

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 12

Thom A - Friday, November 30, 2018 4:39 AM
Then your only method is Dynamic SQL. With a large amount of data, this WILL perform poorly, as you are having to refer to the table dbo.Profile for every Profile. Like I said, the better option is to fix your data set up.
USE Sandbox;
CREATE TABLE dbo.[Profile] (ProfileID int, SomeValue varchar(10));
INSERT INTO dbo.[Profile](ProfileID, SomeValue)
VALUES(1,'asdh');
CREATE TABLE dbo.ProfileData1 (SomeOtherValue varchar(10));
INSERT INTO dbo.ProfileData1 (SomeOtherValue)
VALUES('asdkajhsd'),
('asdklasb');
INSERT INTO dbo.[Profile](ProfileID, SomeValue)
VALUES(2,'asdh');
CREATE TABLE dbo.ProfileData2 (SomeOtherValue varchar(10));
--Leaving this one empty
GO
DECLARE @SQL nvarchar(MAX);
SET @SQL = STUFF((SELECT NCHAR(10) +
          N'UNION ALL' + NCHAR(10) +
         N'SELECT P.ProfileID,' + NCHAR(10) +
         N' P.SomeValue,' + NCHAR(10) +
         N'   COUNT(PD.[Row]) AS Records' + NCHAR(10) +
         N'FROM dbo.[Profile] P' + NCHAR(10) +
         N' OUTER APPLY(SELECT 1 AS [Row] FROM dbo.' + QUOTENAME(N'ProfileData' + CONVERT(varchar(10),P.ProfileID)) + N') PD' + NCHAR(10) +
         N'WHERE P.ProfileID = ' + QUOTENAME(P.ProfileID,N'''') + NCHAR(10) +
         N'GROUP BY P.ProfileID,' + NCHAR(10) +
         N'    P.SomeValue'
      FROM dbo.[Profile] P
      ORDER BY P.ProfileID ASC
      FOR XML PATH(N'')),1,11,N'') + N';';
PRINT @SQL;
EXEC sys.sp_executesql @SQL;
GO
DROP TABLE dbo.ProfileData2;
DROP TABLE dbo.ProfileData1;
DROP TABLE dbo.[Profile];
If you need to debug the code, PRINT is your best friend (or SELECT, if more than 4,000 characters).

Thanks a Lot Thom, this worked like a charm 🙂 Had to play around a bit as I needed this to be called from VB.NET. Now its working.
Thanks Again for all the time and guidance.
Kind Regards,