CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
clement
clement

Reputation: 9

Dynamic SQL can make it?

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.

Upvotes: 0

Views: 63

Answers (2)

MatBailie
MatBailie

Reputation: 86716

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

Upvotes: 1

Stu
Stu

Reputation: 32599

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

Upvotes: 1

Related Questions