Dynamic SQL can make it?

Question

I have one table named AskLists with 2 fields :

AskListId   AskInterview
2032    5
2032    3
2032    4
2032    6
5076    1
5076    3
5076    4
5076    5
5076    6

For each distinct number of the field AskListId, there is a table named by the number AskList'Number' For example AskList5076 :

AskInterview    QUOTA7
1   5660424
2   5660424
3   5660424
4   5667511
5   5667511
6   5667511
7   5667511

So there are hundred of tables AskList'Number'.
I am trying to add the field QUOTA7 which we can find in every AskList'Number' table to the main table AskLists.
For one table, it's easy :

    SELECT  AskListId
        , AskInterview
        ,AskList5076.QUOTA7
     
  FROM [Lists].[dbo].[AskLists]

  INNER JOIN AskList5076 on AskLists.AskInterview = AskList5076.AskInterview

But I need to make it for all the values inside AskListId...
Is it possible to make it without making manually hundred of requests ?
I was told Dynamic SQL could make it but I don't know about it.

Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product and dynamic SQL is highly vendor specific. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , May 10 '21 at 07:27
This is a poor and unscalable data model, but yes you would need Dynamic sql. Anything more will need you to tag your database platform. — Stu, May 10 '21 at 07:52
Are you able to change your table design ? Table like `AskList5076` is a not recommended at all — Squirrel, May 10 '21 at 08:14
No it's table from a software editor. There is an AskiList table incremented for every new project we create. So there are hundred of AskList tables. — clement, May 10 '21 at 08:24
I *strongly* suggest you change your data model. Just for a start, any basic query will probably take longer to compile than to execute. And obviously querying is going to be very hairy. Perhaps create one table you can dump everything into, and copy the data in every time it is inserted, maybe with triggers? — Charlieface, May 10 '21 at 08:33

Stu · Answer 1 · 2021-05-10T09:57:06.423

Have a look at the following and see if this works for you. It might require some slight tweaking and of course modifying to be an update, but in the context of your desired query shown above this should give you the values from each table.

declare @sql nvarchar(max)=''

select @sql +='
 select al.AsklistId, al.AskInterview, aq.quota7 
 from lists.dbo.AskLists al join ' 
 + QuoteName(table_name) 
 + ' on al.AskInterview=aq.AskInterview' 
 + Iif(Count(*) over()=Row_Number() over(order by (select null)),'',' union all')
from INFORMATION_SCHEMA.COLUMNS
where table_name like 'Asklist%'
 and table_name != 'Asklist'
 and column_name='QUOTA7'

print @sql
-- exec sp_executesql @sql

Thank you Stu but the aq.quota7 has to be named by the variable number. In my exemple AskList5076.QUOTA7 — clement, May 10 '21 at 11:29

MatBailie · Answer 2 · 2021-05-10T13:10:12.913

You could use Dynamic SQL to union all your tables together before you join them.

Essentially putting them into the style of structure they should have been in in the first place

Something like this (it's more than a bit hacky)...

DECLARE @union NVARCHAR(MAX)

SELECT
  @union =
    STRING_AGG(
      CAST(N'SELECT ''' + TABLE_NAME + N''' AS source_table, * FROM ' + TABLE_CATALOG + N'.' + TABLE_SCHEMA + N'.' + TABLE_NAME AS NVARCHAR(MAX)),
      CHAR(13)+CHAR(10) + N'UNION ALL' + CHAR(13)+CHAR(10)
    )
FROM
  INFORMATION_SCHEMA.TABLES
WHERE
      TABLE_NAME    LIKE 'AskList%'
  AND TABLE_NAME     <>  'AskLists'
  AND TABLE_CATALOG   = 'Lists'
  AND TABLE_SCHEMA    =  'dbo'
;

DECLARE @sql NVARCHAR(MAX) = N'
WITH
  unioned
AS
(
' + @union + '
)
SELECT
    AskLists.AskListId
  , AskLists.AskInterview
  , unioned.QUOTA7
FROM
  AskLists
INNER JOIN
  unioned
    on  unioned.source_table = ''AskList'' + CAST(AskLists.AskListID AS VARCHAR(MAX))
    and unioned.AskInterview = AskLists.AskInterview
'

EXEC sp_executesql @sql

Demo: db<>fiddle

Thank you MatBailie. I get this error : STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation — clement, May 10 '21 at 12:36
@clement - Cast the first input to the `STRING_AGG()` to a `NVARCHAR(MAX)` *(Answer and fiddle updated)* — MatBailie, May 10 '21 at 13:09

Dynamic SQL can make it?

2 Answers2