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!

Forland

Shouldn't you be passing your SQL query as a character string? (x <- "#tralala"). – Cirrostratus

The paste function creates the string. How would you go about doing this? – Forland

paste("#", "tralala", sep = ""). You "tralala" can also be a variable. See ?paste or help(paste). – Cirrostratus

Can you be more specific about what isn't working? Have you tried running one sql command at a time? – Christinchristina

I think the paste is just putting all the lines together from the sql query...after it has been read in with readLines.... – Reseta

No errors occur, but the output doesn't contain any data – Forland

Answer 1

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.

Cheat

Answer 2

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

Lenzi