Search code examples
sql-serverrbcp

Convert SQL Server 2008 query to bcp query

I have a query that I'm using to create an input file for another program. I'm trying to do the process using the bcp utility via an R script using the system command. The query is as follows (using SQL Server 2008 R2):

SELECT TOP 1000 
case when [click] = 1 then 1
else -1
end  
+  ' ' 
+ '|'
+ 'hr ' hour
, C1
from [mydb].[dbo].[table1]

When I try to run the following in CMD things are fine :

C:\Users\me>bcp "SELECT top 1000 case when click = 1 then 1 else -1 end, + ' |hr ' hour, C1 from [mydb].[dbo].[table1]" queryout "C:\\Users\
\me\\Desktop\\bcp_test.txt" -T -c -t

However when I take this and try to run it in R using:

system('bcp "SELECT top 1000 case when click = 1 then 1 else -1 end, + ' |hr ' hour, C1 from [mydb].[dbo].[table1]" queryout "C:\\Users\
    \me\\Desktop\\bcp_test.txt" -T -c -t')

I get errors about string constants and Status 1 messages. Anything seem off?

Solution

  • It looks like you have a few issues:

    • You are concatenating strings with numbers (i.e. the CASE returns a number but then it is being concatenated with other stuff)
    • You have an extraneous comma just after the END of the CASE statement
    • And I don't know how R escapes embedded quotes, but your main problem is in the concatenation of + ' |hr'

    Assuming that R uses a back-slash to escape embedded single-quotes, the command should be:

    system('bcp "SELECT TOP 1000 CASE WHEN [click] = 1 then \'1\' else \'-1\' END + \' |hr \' AS [hour], [C1] FROM [mydb].[dbo].[table1];" queryout "C:\\Users\\me\\Desktop\\bcp_test.txt" -T -c -t')