Send Column Name as a Parameter

Transact-SQL
3

How are you creating the CSV file in the first place? That should not strip out the leading zeroes if the columns are defined correctly when output. If you open the file in Excel - Excel 'evaluates' the column and determines it is a number and displays it without the leading zeroes.

The source CSV file still has the leading zeroes until you save it from Excel.

My next question: how are you uploading this file?

And finally - if you believe this must be done with a post update script, then you should only perform the update one time across all columns instead of individual updates for each column. Something like this:

 Update MyImport
    Set Column1 = right(concat('00000', Column1), 5)
      , Column2 = right(concat('00000', Column2), 5)
      , Column3 = right(concat('00000', Column3), 5);

If you what to limit the update statement (which isn't really necessary):

 Update MyImport
    Set Column1 = right(concat('00000', Column1), 5)
      , Column2 = right(concat('00000', Column2), 5)
      , Column3 = right(concat('00000', Column3), 5)
 Where len(Column1) < 5
    Or len(Column2) < 5
    Or len(Column3) < 5;

Now - if you are importing the data to a staging table, then you just need to add the above code to the code that processes the data into the final table.

1 Like