question

amitsrivastava-0957 avatar image
0 Votes"
amitsrivastava-0957 asked LiHongMSFT-3908 edited

SQL Dynamic Update Script

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



sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered LiHongMSFT-3908 edited

Hi @amitsrivastava-0957
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.