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

DataNerd 21 Reputation points

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,581 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,456 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points

    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.

    0 comments No comments

  2. Erland Sommarskog 92,956 Reputation points

    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.

    0 comments No comments

  3. LiHong-MSFT 10,031 Reputation points

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

    UPDATE table_name  
    SET Date_column1=nullif(Date_column1,'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:

    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)  
    	SET @Update_String= @Update_String + ''+@Column_name+'=NULLIF('+@Column_name+',''1900''),'  
    	FETCH NEXT FROM Cursor_Test INTO @Column_name  
    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,

    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.

    0 comments No comments

  4. Tom Phillips 17,686 Reputation points

    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.

    0 comments No comments