February 6, 2019 at 11:56 am
I have a table with an Hours column that allows null and has a default constraint of 0. When I'm importing from an Excel file into that table from SSIS or a standard query it defaults all the null values to 0. When I tried importing to this table from a linked server, it populated null values in the table as if it skipped the default constraints. Is this a linked server limitation or did I miss something in configuring the linked server? I used basic syntax:
insert into Table (Col1, Col2, etc.) select F1, F2, etc. from LinkedServer…[TabName]
February 6, 2019 at 12:21 pm
Excel does not have a NULL value. It has a BLANK value which is equivalent to ''. When '' is converted to numeric, it is converted as 0. You may need to have a step to differentiate '' from '0', but you'll need to do that before converting to numeric.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 6, 2019 at 12:26 pm
SSIS behaves differently from linked server.
What is happening there is that linked server is setting the column to null - which is not the same thing as not setting the column (which would kick off the constraint)
small example to exemple
if object_id('dbo.test') is not null
drop table dbo.test;
create table dbo.test
(id int identity (1,1)
,value0 varchar(50) null
,value1 varchar(10) null default 'abc'
,value2 varchar(10) null default 'def'
)
insert into dbo.test (value0, value1, value2) select 'both values','b', 'c'
insert into dbo.test (value0, value1) select 'only value 1','b'
insert into dbo.test (value0, value2) select 'only value 2','b'
insert into dbo.test (value0, value1, value2) select 'both values set to null', null, null
select *
from dbo.test
if object_id('dbo.test') is not null
drop table dbo.test;
output
id value0 value1 value2
1 both values b c
2 only value 1 b def
3 only value 2 abc b
4 both values set to null NULL NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply