question

Gazz avatar image
Gazz asked

How to update a column where column is based on a table list

Hello all, I have an import via StreamReader that loads blanks as a blank But for date columns I want the blanks to be NULL. So I started with something like this: UPDATE [tablename] SET [datecolumn1] = NULL WHERE [datecolumn1] = '' UPDATE [tablename] SET [datecolumn2] = NULL WHERE [datecolumn2] = '' UPDATE [tablename] SET [datecolumn3] = NULL WHERE [datecolumn3] = '' But this takes a lot of typing, as there are lots of columns and lots of tables - so want to do it dynamically. I can get all the date columns from INFORMATION_SCHEMA.COLUMNS. So how would I loop through this list and update all the columns? The only way I have at the moment is to loop through the list in .net and do individual UPDATE statements. This is good enough performance/time wise, but was hoping there is a more elegant solution. Thanks
updatecolumnsvariable
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
If you must use the INFORMATION_SCHEMA columns table, then you could use somethig like this to generate & execute your statement: DECLARE @sql NVARCHAR(MAX) SELECT @sql = ISNULL(@sql,N'') + N'UPDATE ' + QUOTENAME(c.TABLE_SCHEMA) + N'.' + QUOTENAME(c.TABLE_NAME) + N' SET ' + QUOTENAME(c.COLUMN_NAME) + ' = NULL WHERE ' + QUOTENAME(c.COLUMN_NAME) + ' = ''''; ' FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.DATA_TYPE = 'datetime' AND c.IS_NULLABLE = 'YES' SELECT @sql -- check what you're building first... --EXEC (@sql) -- Uncomment this line when you're happy with what you see in @sql
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That is amazing. Thank you very much
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.