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!

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.