Running SQL query through RStudio via RODBC: How do I deal with Hash Tables?

Question

I've got a very basic SQL query that I'd like to be able to view in R.

The trouble is, I need to be able to reference a #table:

    select
    RAND(1) as random
    into #test

    select * from #test

Is this possible, or will I need to create permanent tables, or find some other work around?

I currently do this via a RODBC script which allows me to choose which SQL file to run:

    require(RODBC)
    sql.filename <- choose.files('T:\\*.*')
    sqlconn <- odbcDriverConnect("driver={SQL Server};Server=SERVER_NAME;Trusted_Connection=True;")
    file.content <- readLines(sql.filename)
    output <- sqlQuery(sqlconn, paste(file.content[file.content!='--'],collapse=' '))
    closeAllConnections()

Do you have any advice on how I can utilise #tables in my SQL scrips in R?

Thanks in advance!

Shouldn't you be passing your SQL query as a character string? `(x <- "#tralala")`. — Roman Luštrik, Aug 22 '13 at 09:55
The paste function creates the string. How would you go about doing this? — , Aug 22 '13 at 10:00
`paste("#", "tralala", sep = "")`. You "tralala" can also be a variable. See `?paste` or `help(paste)`. — Roman Luštrik, Aug 22 '13 at 12:30
Can you be more specific about what isn't working? Have you tried running one sql command at a time? — joran, Aug 22 '13 at 13:00
I think the paste is just putting all the lines together from the sql query...after it has been read in with `readLines`.... — h.l.m, Aug 22 '13 at 14:28

score 2 · Answer 1 · answered Sep 08 '15 at 15:26

2

When you use temp tables SQL outputs a message with the number of rows in the table. R doesn't know what to do with this message. If you begin your SQL query with SET NOCOUNT ON SQL will not output the count message.

answered Sep 08 '15 at 15:26

Cody

21
2

score 0 · Answer 2 · answered Sep 06 '13 at 19:55

I use #tables by separating my query into two parts, it returns character(0) if I do like:

sqlQuery(test_conn, paste("
drop table #test;
select
       RAND(1) as random
     into #test

select * from #test
"))

So instead I would use:

sqlQuery(test_conn, paste("
drop table #test;
select
       RAND(1) as random
    into #test
"))

sqlQuery(test_conn,"select * from #test")

It seems to work fine if you send one Query to make the #table, and a second to retrieve the contents. I also added in drop table #test; to my query, this makes sure there is not already a #test. If you try to write to a #table name that is already there you will get an error

Running SQL query through RStudio via RODBC: How do I deal with Hash Tables?

2 Answers2