Update with dynamic tables

Question

I have to write update using dynamic sql becaus i know only name of column that I want to update and names of columns which I will use to join tables in my update. But I don't know the numbers of tables and names. Names of tables I will get in parameter of my procedure in this way

declare @Tables = N'Customer,Employee,Owner'

So I want to have update like this:

update t 
    set [Status] = 100
from 
   TemporaryTable t 
   left join Customer t1 on t1.RecordId = t.RecordId 
   left join Employee t2 on t2.RecordId = t.RecordId 
   left join Owner t3 on t3.RecordId =t.RecordId

   where 
      t1.RecordId is null 
      and t2.RecordId is NULL
      and t3.RecordId is null

I know that each table will have column RecordId and want to left join this tables to my TemporaryTable on this column but I don't know the names and numbers of tables. For example I will have one, two, or ten tables with different names. I know that this tables names will be save in parameter @Tables in that way:

 @Tables = N'Customer,Employee,Owner'

There is possilble to write this update in dynamic way?

How do you determine the JOIN criteria for all of these tables? This seems like an XY problem. — Thom A, Jun 05 '19 at 06:40
Every table will have field RecordId and I will join on this field, but I don't know the names of tables and numbers. — NewMe, Jun 05 '19 at 06:55
if you use `left join` only to remove rows, it would make life easier for you and the answerers to use `not exists` instead — George Menoutis, Jun 05 '19 at 06:58
Tables aren't parameters, they are the most fundamental part of the query. The execution plan created for each query *depends* on the table, its indexes and the statistics. If you change one table for another you'll get *very* different execution plans. — Panagiotis Kanavos, Jun 05 '19 at 06:59

Zhorov · Accepted Answer · 2019-06-05T07:39:15.147

This is an answer, which helps ... to write update using dynamic sql ... and only shows how to generate a dynamic statement. It's based on string splitting. From SQL Server 2016+ you may use STRING_SPLIT() (because here the order of the substrings is not important). For previous versions you need to find a string splitting function.

T-SQL:

DECLARE @Tables nvarchar(max) = N'Customer,Employee,Owner'
DECLARE @join nvarchar(max) = N''
DECLARE @where nvarchar(max) = N''
DECLARE @stm nvarchar(max) = N''

SELECT 
   @join = @join + CONCAT(
      N' LEFT JOIN ',
      QUOTENAME(s.[value]),
      N' t',
      ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
      N' ON t',
      ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
      N'.RecordId = t.RecordId'
   ),
   @where = @where + CONCAT(
      N' AND t',
      ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
      N'.RecordId is NULL'
   )
FROM STRING_SPLIT(@Tables, N',') s
SET @stm = CONCAT(
   N'UPDATE t SET [Status] = 100 ',
   N'FROM TemporaryTable t',
   @join,
   N' WHERE ',
   STUFF(@where, 1, 5, N'')
)

PRINT @stm
EXEC sp_executesql @stm

Notes:

One note, that I think is important - consider passing tables names using table value type for parameter, not as comma-separated text.

score 0 · Answer 2 · answered Jun 05 '19 at 06:57

It seems like this will suit your needs, though I don't fully understand what you're trying to do. Here we're constructing the final SQL in two pieces (@s and @where) and then concatenating into the final SQL at the end.

declare @Tables varchar(100) = N'Customer,Employee,Owner'
declare @tablenames table (tablename nvarchar(100))

insert @tablenames (tablename)
select value
  from string_split(@Tables, ',');

declare @where varchar(max) = ''
declare @s varchar(max) = '
update t
   set [Status] = 100
  from TemporaryTable t'

select @s += '
  left join ' + tablename + ' on ' + tablename + '.RecordId = t.RecordId'
     , @where += case when @where = '' then '' else ' and ' end + tablename + '.RecordId is null
     '
  from @tablenames

print @s + char(13) + ' where ' + @where
exec( @s + char(13) + ' where ' + @where)

Update with dynamic tables

2 Answers2