question

NaomiNNN avatar image
0 Votes"
NaomiNNN asked NaomiNNN edited

Unable to post the following reply (tried 3 times)

I tried to post the reply which follows 3 or more times and I was getting an error requesting me to report in site's feedback. I reported, then I was also able to post in the original thread. Anyway, here is goes - originally I was unable to post it:


I tried the following code to generate update statement dynamically in the statement column but somehow it's not working properly for me and I don't see why:

 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' -- 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(QUOTENAME(column_name) + '=' +
 QUOTENAME(REPLICATE('Z', [Length]),''''), ',') AS [Statement]
 FROM cte2 
 GROUP BY id, cte2.TABLE_SCHEMA, cte2.TABLE_NAME

I verified that select * from cte2 returns information correctly, but the statement column is not concatenating all values properly as it should. Some columns are big (500 characters), but it should not be an issue? May be the problem is with the replicate function ?

sql-server-transact-sqlqna-feedback
· 1
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.

I was able to post it as a reply to someone, but I originally had that issue of posting this code that's why I reported here in site's feedback. Please, why did you change the tag as I was reporting the problem with the site, not the issue with the code itself, which, BTW, also has an interesting problem I'm going to post a separate thread about? In other words, this was my original reply to someone's question which I had trouble posting (tried 3 or more times). There is an interesting problem with that code too, but this may be a bug inside SQL Server.

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered

Ok, since this thread was moved to this forum already, let's continue the conversation. This code doesn't work correctly when my columns are varchar(500) characters. I tried this simple example with just a few columns. I also tried to cast to varchar(max) inside the string_agg function, but still result was only returning columns which are varchar(100) or less and the columns which are varchar(500) are not included into the final result. Do you see what may be an issue with the code? I can switch to use FOR XML PATH('') approach but I found it odd that string_agg function doesn't work silently. To me it looks like a SQL Server bug. Can anyone else reproduce, please?

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

UPDATE. I figured out my mistake. I was using quotename function which is limited to 128 characters. Once I changed my code this way it worked:

 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.