SQL Dynamic Query from multiple tables

Question

Hi All,

Below is the scenario for which i need help. Please check.The user passes Effective date as paramter.
The query should get Table 1 details by effective date

Table 2 has Table details as below

I need a for loop which does the the following:

The Query should take Table name from Table 1 and form dynamic query to get equivalent schema and Table names from Table2

The do a select count(*) from each Table name for a File version ID found in Table 1.

**Dynamic query Ex : select count(*) from Table2.schema1.File1_table where Table1.File Version ID=101**  

Display final results File Name and Count as output

Answer 1

Is this a real problem or some kind of exercise or interview question?

If this is a real problem, what lead to this design? Having dynamic table names is rarely the right thing, but usually a token of a design mistake.

In any case, for this kind of question, we recommend that you post CREATE TABLE statements for your tables and INSERT statements with sample data. And if there are more tables that these Table1 and Table2 are pointing, we need some examples of these as well. I would also recommend that you a few more rows to your sample data than the two you have above.

This serves to clarify the problem, so we more easily can answer your question. Also, with everything described in SQL we can copy and paste into a query window to develop a tested solution.

Answer 2

I've created a copy of the two database tables and to get the required final result, with file name and count - you could do something like the below. As mentioned above, without knowing the full database structure and names, I've put "{table1 reference here}" so you can put in your own database table names

SELECT tb1.[File Name], COUNT(*) as [Count]
FROM {table1 reference here} as tb1 INNER JOIN
{table2 reference here} as tb2 ON 
tb1.[Table Name] = tb2.[Table name]
GROUP BY tb1.[File Name]

For example - the test I ran used the following;

SELECT tb1.[File Name], COUNT(*) as [Count]
FROM [Table1] as tb1 INNER JOIN
[Table2] as tb2 ON 
tb1.[Table Name] = tb2.[Table name]
GROUP BY tb1.[File Name]

Could something like be what you're looking for?

Answer 3

Hi All,

Thanks for reply..regarding design ..its the client DB and they have mainitained like this. where there are lot files and grouped by schema's

Below the snap of All the Table Structure and create scripts are attached with sample data

99281-dynamic-query.txt49-image.png

Final Result expected:

Answer 4

This design is horrible, and should be changed. It is only bound to cause a lot of headache. As you already have noticed.

You need this view:

   CREATE VIEW all_file_tables AS  
      SELECT 'File1_table' AS FileName, File_Version_ID, Name, Qty  
      FROM   sch1.File1_table  
      UNION  ALL  
      SELECT 'File2_table' AS FileName, File_Version_ID, Name, Qty  
      FROM   sch2.File2_table  

Then you can run this query:

   SELECT t1.[File Name], COUNT(*)  
   FROM   [Table 1] t1  
   JOIN   all_file_tables aft ON t1.[Table Name] = aft.FileName  
   GROUP  BY t1.[File Name]  

Obviously, this view needs to be generated when they add new tables. Here is a script to do this. You will need to tweak the filter to find the exact set of tables.

   DECLARE [@](/users/na/?userId=070292c7-7ffe-0006-0000-000000000000) nvarchar(MAX)  
     
   SELECT [@](/users/na/?userId=070292c7-7ffe-0006-0000-000000000000) = 'CREATE OR ALTER VIEW all_file_tables AS ' +   
       string_agg(convert(nvarchar(MAX), '   
                  SELECT ' + quotename(t.name, '''') + ' AS FileName, File_Version_ID, Name, Qty  
                  FROM  ' + quotename(s.name) + '.' + quotename(t.name)), '   
                  UNION ALL')  
   FROM  sys.schemas s  
   JOIN  sys.tables t ON s.schema_id = t.schema_id  
   WHERE s.name LIKE 'sch%'  

You may note that I short-cutted Table 2. If the same table name can appear in multiple schemas, you will have to modify the view definition. That is left as an exercise to the reader.

The above requires SQL 2017. On earlier version you can use FOR XML PATH for string concatenation. Google can help you to find examples on that.

Answer 5

Hi @Ajay Kamath ,

You can use cursor:

CREATE SCHEMA sch1;  
CREATE SCHEMA sch2;  
    
DROP TABLE if exists sch1.File1_table  
CREATE TABLE sch1.File1_table ( [File_Version_ID] varchar(50), [Name] varchar(50), [Qty] varchar(50) )  
INSERT INTO sch1.File1_table VALUES ( '101', 'aaa', '10' ),   
                                    ( '101', 'bbb', '20' ),   
                                    ( '101', 'ccc', '30' )  
DROP TABLE if exists sch2.File2_table  
CREATE TABLE sch2.File2_table ( [File_Version_ID] varchar(50), [Name] varchar(50), [Qty] varchar(50) )  
INSERT INTO sch2.File2_table VALUES ( '102', 'ccc1', '300' ),   
                                    ( '102', 'DDD1', '500' ),   
                                    ( '102', 'FFFDDD1', 'FF500' )  
DROP TABLE if exists ##temp  
CREATE TABLE ##temp([Filename] varchar(20),count int)  
   
DECLARE @EffectiveDate VARCHAR(50)  
DECLARE @sql NVARCHAR(MAX)  
SET @EffectiveDate='2021-10-05'  
  
DECLARE mycursor CURSOR FOR   
SELECT t1.[File Name],[File Version ID],CONCAT(t2.[Schema_Name],'.',t1.[Table Name]) [Table Name]  
FROM [Table 1] t1  
JOIN [Table 2] t2 ON t1.[Table Name]=t2.[Table Name]  
WHERE [Effective Date] =@EffectiveDate    
OPEN mycursor    
   
DECLARE @filename VARCHAR(4000),@fileversionid NVARCHAR(4000),@TableName NVARCHAR(4000)  
FETCH NEXT FROM mycursor INTO @filename,@fileversionid,@TableName  
   
WHILE @@FETCH_STATUS=0    
BEGIN  
    SET @sql= 'insert into ##temp select '''+@filename+''',count(*) from '+@TableName +' where [File_Version_ID]='+@fileversionid  
    EXECUTE sp_executesql  @sql  
    FETCH NEXT FROM mycursor INTO @filename,@fileversionid,@TableName  
END  
   
CLOSE mycursor    
DEALLOCATE mycursor   
  
SELECT * FROM ##temp   

Output:

It is worth noting that the cursor will affect performance.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.