SQL Dynamic Update Script

Question

Hey Folks!

I need to update several columns of several SQL tables based on corresponding entries in one another table (Master Table).

Structure of Master Table

Table Name Column Names

Table_A Table_A_Col_A ,Table_A_Col_B , Table_A_Col_C
Table_B Table_B_Col_D, Table_B_Col_E, Table_B_Col_F

i.e. I have name of the table and all columns which need to be update, also all column will be updated with value 'ZZZZ' where number of Z will be equal to the length of that corresponding column.

I am looking for Dynamic SQL Script that will update the tables based on the entries in the Master table, i.e. let say for e.q. on first iteration, Table_A_Col_A ,Table_A_Col_B , Table_A_Col_C of Table_A will get updated and then for Table_B and so on.

Thanks
Amit Srivastava

Answer 1

Try the following code to generate the update statements dynamically. Once you generated them you can either loop through the table and execute each statement using sp_ExecuteSQL procedure or you can simply highlight the column, copy values into a separate query window and execute.

DECLARE @t TABLE (id INT IDENTITY PRIMARY KEY, [Table Name]  VARCHAR(30), [Column Names] VARCHAR(MAX))

INSERT INTO @t ([Table Name], [Column Names])
VALUES
    ('EVENT_TYPE' -- Table Name - varchar(30)
     , 'flow_nm, event_type_txt, event_type_status_txt' -- Column Names - varchar(max)
    ), ('event_tracking_type', 'event_tracking_type_nm, event_tracking_type_desc, active_ind')
SELECT * FROM @t;

;WITH cte AS (SELECT id, [table name] AS table_name, LTRIM(RTRIM(v.value)) AS column_name FROM @t t

CROSS APPLY STRING_SPLIT([t].[Column Names], ',') v 
), 

cte2 AS (SELECT cte.id, col.TABLE_SCHEMA, col.TABLE_NAME, col.COLUMN_NAME,
col.CHARACTER_MAXIMUM_LENGTH AS [Length] FROM cte  INNER JOIN INFORMATION_SCHEMA.columns col ON 
cte.table_name = col.TABLE_NAME AND cte.column_name = col.COLUMN_NAME AND col.TABLE_SCHEMA = 'dbo'
AND col.DATA_TYPE LIKE '%char')
--SELECT * FROM cte2
SELECT id, 'UPDATE ' + QUOTENAME(table_name) + ' SET ' + STRING_AGG(CAST(QUOTENAME(column_name) + '=''' AS VARCHAR(MAX)) + 
(REPLICATE('Z', [Length])) + '''', ',') WITHIN GROUP (ORDER BY cte2.Length DESC)

AS [Statement]
FROM cte2 
GROUP BY id, cte2.TABLE_SCHEMA, cte2.TABLE_NAME

Answer 2

Hi @amit srivastava
Please check this:

--Using CURSOR  
DECLARE Update_cursor CURSOR SCROLL  
FOR WITH CTE AS  
(  
 SELECT A.Table_Name,LTRIM(RTRIM(Split_Data.D.value('.','VARCHAR(100)'))) AS Column_name  
 FROM (SELECT Table_Name,CAST('<X>'+REPLACE(Column_Names,',','</X><X>')+'</X>' AS XML)AS STRING FROM Master_Table)A  
 CROSS APPLY A.STRING.nodes('X')AS Split_Data(D)  
)  
SELECT C.*,col.CHARACTER_MAXIMUM_LENGTH AS [Length]  
FROM CTE C INNER JOIN INFORMATION_SCHEMA.columns col ON C.Table_name = col.TABLE_NAME AND C.Column_name = col.COLUMN_NAME AND col.TABLE_SCHEMA = 'dbo' AND col.DATA_TYPE LIKE '%char'  
  
OPEN Update_cursor  
--Declare the variable to be stored in the cursor to extract the data  
DECLARE @Table_Name VARCHAR(50) ,@Column_name VARCHAR(50), @Length INT  
  
FETCH FIRST FROM Update_cursor INTO @Table_Name,@Column_name,@Length    
--The number of variables must be the same as the number of columns in the cursor query result set  
WHILE @@fetch_status=0  --Extraction is successful, proceed to the next data extraction operation  
 BEGIN  
   DECLARE @STRING  VARCHAR(MAX)  
   SET @STRING='Update '+@Table_Name+' Set '+@Column_name+ '=' +''''+REPLICATE('Z', @Length)+ ''''  
   PRINT  @STRING  
   --EXEC (@STRING) --uncomment this when you have a check on the update statement  
   FETCH NEXT FROM Update_cursor INTO @Table_Name,@Column_name,@Length --Move cursor  
 END  
  
DEALLOCATE Update_cursor  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.