Search code examples
sql-serverssmsssms-2012sql-import-wizard

SQL Server Import and Export Wizard Datetime issue

I need to import a flat csv file with datetime column - 'ExecutionDateTime'. This column can have empty values, example :

enter image description here

I am importing this to an existing table with ExecutionDateTime Column is set as Datetime2.

ExecutionDateTime   datetime2,

When I import using SQL Server Import Export wizard, I get value as "0001-01-01 00:00:00.0000000", for the empty row. I want it to Read NULL.

ExecutionDateTime Column is set as Datetime2.

Current behavior:

enter image description here

Expected :

enter image description here Is this possible?

I am using SQL Server 2012.

Any pointers?

Solution

  • I don't think it's possible with the wizard. But, you could use a case statement on the import...

    insert into mytable
select
   Id
   ,case when ExecutionDateTime = '' then null else ExecutionDateTime end
from --openrowset, etc...

    Or, you could always fix it after the fact if the data set is small 0r you are using a staging table...

    update mytable 
set ExecutionDateTime = null
where ExecutionDateTime = ''

    Here's an example using BULKINSERT