Sql-server – How to use Case Statement with BCP

bcpsql server

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.

Best Answer

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.

Related Solutions

Sql-server – Exporting Access DB to SQL Server: unexpected EOF

First, use tab delimited files unless you want to use -t to specify a different delimiter (such as a comma). By default it will accept the tab.

Next, unless you're exporting data with the -n flag, use -c to import the data.

Finally, based on your comments, it looks like there is a mis-match between the size of your table columns and the actual width of the data. That's why you're getting notified the data is being truncated. Match those two up and you'll be all set.

You can check out the MSDN page on bcp for more information on flags, etc.

Sql-server – Use Distinct With Cast As XML

You have 2 issues that I can see:

You cannot pass a VARCHAR(MAX) to xp_cmdshell, largest you can pass is VARCHAR(8000) or NVARCHAR(4000), as per the xp_cmdshell documentation here.
Your temp table (and your Table variable for that matter) are out of scope for other connections, so bcp won't be able to see it.

I worked around these issues with the following:

DECLARE @TestData TABLE (
    pinkpurpleblue VARCHAR(MAX)
)

INSERT INTO @TestData (pinkpurpleblue) VALUES ('xx11'), ('rr22'), ('xx11'), ('rr22')

CREATE TABLE ##distinctValues (
    pinkpb VARCHAR(MAX)
)

INSERT INTO ##distinctValues (pinkpb) SELECT DISTINCT pinkpurpleblue FROM @TestData

DECLARE @file VARCHAR(200), @sql VARCHAR(8000)
SET @file = 'C:\Prod\Results\testing.csv'
SET @sql = 'bcp "SELECT ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpb AS XML) FROM ##distinctValues" queryout "' 
SET @sql = @sql + @file + '" -c -t, -T -S '+@@SERVERNAME
EXEC MASTER..xp_cmdshell @sql 

DROP TABLE ##distinctValues

Here, I make sure that the type of @sql is VARCHAR(8000) rather than VARCHAR(MAX) and I use a global temp table instead of a local one, so that bcp will be able to see it.

Best Answer

Related Solutions

Sql-server – Exporting Access DB to SQL Server: unexpected EOF

Sql-server – Use Distinct With Cast As XML

Related Question