Running SQL query through RStudio via RODBC: how do I work with Hash tables?

Question

I have a very simple SQL query that I would like to see in R.

The problem is that I need to be able to reference #table:

select RAND(1) as random into #test select * from #test 

Is this possible, or do I need to create persistent tables or find another job?

I am currently doing this through a RODBC script that 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 tips on how I can use #tables in my SQL scripts in R?

Thanks in advance!

Answer 1

I use #tables, dividing my query into two parts, it returns a character (0) if I 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") 

This seems like normal if you send one request to make #table, and the second to get content. I also added a drop table #test; on my request this ensures that there is no #test yet. If you try to write the name #table that already exists, you will receive an error message

Answer 2

When using temporary tables, SQL displays a message with the number of rows in the table. R does not know what to do with this message. If you start your SQL query with SET NOCOUNT ON , SQL does not display a counter message.