December 12, 2017 at 1:49 pm
I currently move data from an Oracle 11g DW to SQL Server 2016 by scripting SQLPlus (flat file out) and BCP (flat file in). I would prefer to use a linked server and OPENQUERY. In my testing, it works really well; it's simple, and at least 10x faster.
The only problem is that the old technique (inserting records into my SQL Server table with BCP) put nulls in blank fields (which is what I want), but the OPENQUERY/Linked Server method puts empty strings in null fields, which is a change, and it's causing some issues with my application.
I've been racking my brain, trying to think of how to make the new method replicate the null behavior of the old method. Any suggestions?
insert into mytable select * from OPENQUERY ([mylinkedserver],'select c1, c2, c3 from table_a')
December 12, 2017 at 3:35 pm
You could use "INSERT INTO ... SELECT NULLIF(a, ''), NULLIF(b, ''), ... FROM OPENQUERY()" to convert empty strings to NULL.
December 13, 2017 at 9:39 am
That worked, thank you Scott!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply