CODEWITHSUNDEEP
sql-serverssmsssms-2012sql-import-wizard
ProgSky
ProgSky

Reputation: 2620

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?

Upvotes: 1

Views: 1562

Answers (1)

S3S
S3S

Reputation: 25112

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

Upvotes: 1

Related Questions