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 :CREATE TABLE [dbo].[tmp](
[chart_code] [varchar](20) NOT NULL,
[horizontal_axis_min] [decimal](28, 5) NULL,
PRIMARY KEY CLUSTERED (
)GOALTER TABLE [dbo].[tmp] ADD DEFAULT ((0)) FOR [horizontal_axis_min]
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 )
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!