Convert Empty string into NULL when data insert into table from another table

Question

I am stuck on inserting data from one table into another table and exporting data into csv file using bcp. The problem is that the csv text file contains null instead of '' empty string.

When I insert data into a table from another table the empty string column is treated as a NULL. Doing the bcp command generate correct file.

I am using this command to export bcp

bcp ClientReportNewOrder out "D:\Temp\Neeraj\TestResults\oOR.txt" -c -t"," -r"\n" -S"." -U"sa" -P"123"

and this for insert data into table

insert into ClientReportNewOrder
    select * from ClientReportNewOrder_import

Answer 1

After lots of brainstorming ,I have been found the solution.

1. Execute Insert statement

   insert into ClientReportNewOrder select * from ClientReportNewOrder_import
   

2. After insert record update record by Dynamic query like below.

   DECLARE @qry NVARCHAR(MAX)
   SELECT  @qry =  COALESCE( @qry + ';', '') +
   'UPDATE ClientReportNewOrder SET [' + COLUMN_NAME + '] = NULL 
   WHERE [' + COLUMN_NAME + '] = ''''' 
   FROM INFORMATION_SCHEMA.columns
   WHERE DATA_TYPE IN ('char','nchar','varchar','nvarchar') and   TABLE_NAME='ClientReportNewOrder '
   
   EXECUTE sp_executesql @qry
   

And follows above steps, I have been able to resolved the issue.

If any one have good technique for achieve same please post.