Add A default Value to Multiple columns (>100 columns)

Question

I have a table that I am inserting data from another source (I cannot make any modification on the source ), my table contains more than 100 date columns, all inserting date values, the issue is, the source table has invalid date like '1900' and I am trying to find an easy way to create a default value for all the columns to put a null value when the date is '1900', I don't want to write a case statement with a default value for each column, that will be a night mare. Is there an easy way to do these? Thank you.

Answer 1

Not sure how you import data to the table from the source. Based on the information you provided, I would try to import data to the staging table first and then to use the script to handle the invalid date fields when the destination table is populated from the staging table.

Answer 2

In the end, you will need to have something like try_cast(date, col) for each these columns. You could use some dynamic SQL to generate it, though. Or simply employ you find/replace skills in a good text editor.

Answer 3

Hi @DataNerd
You can also update the table like this :

UPDATE table_name  
SET Date_column1=nullif(Date_column1,'1900'),  
    Date_column2=nullif(Date_column2,'1900'),  
    Date_column3=nullif(Date_column3,'1900'),  
    ......  

Considering that there are more than 100 date columns in the table,you can use cursor to help you organize the Update statement,check this:

GO  
DECLARE Cursor_Test CURSOR LOCAL  --local cursor  
FOR SELECT Name FROM SysColumns Where id=Object_Id('table_name') --Query all column names in your table  
  
OPEN Cursor_Test   
DECLARE @Column_name NVARCHAR(15)  
DECLARE @Update_String NVARCHAR(MAX)  
SET @Update_String= 'UPDATE table_name SET '  
  
--Loop and fetch   
FETCH NEXT FROM Cursor_Test INTO @Column_name  
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
	SET @Update_String= @Update_String + ''+@Column_name+'=NULLIF('+@Column_name+',''1900''),'  
	FETCH NEXT FROM Cursor_Test INTO @Column_name  
END  
SET @Update_String=LEFT(@Update_String,len(@Update_String)-1)  
PRINT @Update_String  
CLOSE Cursor_Test   
  
DEALLOCATE Cursor_Test   

Then you can copy the update statement obtained by print @update_zhang _String, remove some of the columns that are not dates, and then update.

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.

Answer 4

There is no shortcut. You have 100 columns and would have to create 100 commands to convert 100 fields.

Without seeing your table schema's, it is not possible to really help. You might be able to pivot the data and fix them all at once.

But this is a case where it would likely be beneficial to import the source table into a "staging" table. Then modify the data in the staging table, before inserting it into the target table.