Importing an entire SQL Server table into R

Question

Closed. This question needs to be more focused. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65906802/importing-an-entire-sql-server-table-into-r

Answer 1

You can use sqlcmd for bulk queries. It is far faster than anything Microsoft's ODBC drivers (or FreeTDS) can do.

If your query is (say)

SELECT Id, Field1, Field2 FROM TableName WHERE Field1 = 'QUUX'

Then your command line would be something like:

sqlcmd -S serveraddress -d databasename 
  -U username -P '*PASSWORD*' 
  -W -s , -o out.csv 
  -Q "SET NOCOUNT ON; SELECT Id, Field1, Field2 FROM TableName WHERE Field1 = 'QUUX'"

(If your server is on a non-standard port, then use -S serveraddress,portnumber.)

I tend to not like R's system (or system2, a thin veneer), instead preferring the processx package for its safer way of handling command line arguments.

bgquery <- processx::run("sqlcmd",
    args = c("-s", "serveraddress", "-d", "databasename",
             "-U", "username", "-P", "*PASSWORD*", "-W", "-s", ",", "-o", "out.csv",
             "-Q", "SET NOCOUNT ON; SELECT Id, Field1, Field2 FROM TableName WHERE Field1 = 'QUUX'"),
    error_on_status = FALSE, echo_cmd = FALSE, echo = FALSE)

# check its stdout, it can be rather verbose
tail(bgquery$results$stdout)

# check the return value, typically "0" for command success
bgquery$results$status

# if error, look at the error output, something might be in the stdout above, too
bgquery$results$stderr

At this point, read in the file:

x <- data.table::fread("out.csv", nrows = 3, na.strings = c("NA", "", "NULL"))

Note: sqlcmd does not quote the values. So for instance,

SELECT 'hello,world' as A

using sqlcmd will return

A
hello,world

which is obviously broken. If this is a problem, you may be stuck. bcp is another option with its own problems, so it's not a perfect replacement.