Tony Rogerson's ramblings on SQL Server

This has driven me mad for the last 15 minutes until I profiled it to confirm I was actually importing data into the right server and database.

On the Import/Export wizard in Management Studio on the mappings I selected DELETE existing data.

My destination table is as follows :

)
GO

Notice that my horizontal_axis_min column is NULLable and I've got a DEFAULT constraint on the column for 0.

My source data contains a mixture of NULL and 0's for horizontal_axis_min, guess what - the NULL's in the source data get erradicated and the DEFAULT constraint applied!

This is not the normal behaviour with the INSERT statement, the NULL is kept....

insert tmp( chart_code, horizontal_axis_min ) values( 'test', null )
     select *
     from tmp

Looking at profiler (see below) the statement doing the insertion is using the bulk insert operator....

insert bulk [dbo].[tmp]([chart_code] varchar(20) collate Latin1_General_CI_AS,[horizontal_axis_min] decimal(28,5))with(TABLOCK,CHECK_CONSTRAINTS) 

I've not an answer for the problem as yet, except disable or drop the default constraints before you insert the data; be interesting to see what happens if I specified create the destination table - alas, I've not time at present.

Watch the trap!

Tony.