How to use Case Statement with BCP

Question

I am using BCP as follows,

bcp "SELECT * FROM [dbo].[GpsPoint] where id in (551043, 551044, 551045)" -n

This works 100%. I have omitted the rest of it because it is just server, user, password settings etc.

If I change the query to this,

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

With a case statement the BCP import gives this,

Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid date format
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

The contactNo field is an INT NOT NULL in both source and destination, so I cannot see why translating the value using case then casting it to INT is not working?

My BCP import looks like this (minus the server, user, password etc),

bcp GpsPoint IN "c:\temp\GPSTracking\gpspoint.dat" -n

UPDATE:

If I change the query to this it works,

SELECT id, queueid, 8022 as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

So it is definitely the case statement. And the issue does not resolve if I use ELSE 0 in the case statement. On my actual query I have a where clause (the where clause on the very first query above) which selects only 3 records as a proof of concept, and they all have 8022 as the value. The ELSE 0 never gets used if I add it and I still get the same error.

Answer 1

As you mentioned the contactNo is INT NOT NULL, in your CASE statement currently you are handling only for the value 8022 and not for the remaining values. If you add the ELSE 0 in the case statement it will solve the issue. It will select 0 for the entries other than contactNo = 8022

So the working code will be:

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 ELSE 0 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing 
FROM [dbo].[GpsPoint]

More explanation:

CASE contactNo WHEN 8022 THEN 1234 END this statement will return 1234 for the contactNo is 8022 and for the remaining entries it return only NULL.
So the CAST(NULL AS INT) also returns NULL only.
So inserting the NULL value into the NOT NULL value causing the error.