This is a feature request in response to RStudio 1.2 Preview: SQL Integration - Posit
EDIT: See post here in related issue
As I currently understand it, RStudio 1.2 gives us the ability to write a SQL query in a .sql script and preview it using DBI by merely adding ""-- ! preview conn=con" as a header to the file. This is great and I am already using this feature, but it would be a dramatic improvement if we had the ability to have the most recently added query be the one that DBI sends to the connection so we can preview it.
As it stands right now, I am able to write multiple queries in the .sql script and hit preview or just save the file, and DBI will send the query that is nearest to the TOP of the .sql script to be previewed. In a typical workflow, I think most people would like to add queries to the bottom of their script to reference tables or SELECT queries they have already defined, so having the SQL preview use the LAST query in the file would allow this SQL instant preview functionality to behave as a bona-fide SQL editor.
I have read other requests (see link at top) from someone to have similar functionality, but the response was that DBI was not designed to handle multiple queries at a time. My issue is not that I want to send multiple queries simultaneously (although that may be of interest - I can currently select multiple queries and run "execute queries as separate statements" in Teradata SQL Assistant to create multiple tables without having to select them one at a time, which is nice). Rather, I want the single query being sent to the database to be changed from the query at the top of the file to the query at the bottom of the file. My temporary workaround is to write queries top-to-bottom as I would in any SQL editor and then comment out each query except for the very last one. This is very tedious and makes me want to go outside of RStudio again to my SQL editor, which I think defeats the spirit of meeting all of your sql needs inside of RStudio along with your R analysis.
Thanks for considering this request and please let me know if there is any way I can address this issue currently!
-Zack Larsen
Example:
Let's say I have a SQL file that has the queries I want to send to my database, for which I have an odbc driver installed. In my case, I use Teradata at work and use odbc and DBI to connect. Here is what an example sql script might look like (let's call it myquery.sql):
SELECT TOP 10 *
FROM mydatabase.transactions
ORDER BY col1, col2 DESC;
SELECT a.DATE
,a.TRANSACTION_NUMBER
,b.FIRST_NAME
,b.LAST_NAME
FROM mydatabase.transactions a
INNER JOIN mydatabase.customerinfo b
ON a.ID = b.ID;
If I leave my Teradata SQL Assistant editor, I can take the same 'myquery.sql' script and open it in RStudio 1.2, add a header to the script that defines my Teradata odbc connection:
-- ! preview conn=Teradatacon
... and then I am able to simply save this script and DBI will take the query for me and send it to Teradata and the results will show up in the "SQL Results" tab. This is better than putting the query in a string and using dbFetch() or dbSendQuery(), but out of the two queries I have written, the one nearest to the top of the file is the one that gets sent to preview:
SELECT TOP 10 *
FROM mydatabase.mytable
ORDER BY col1, col2 DESC;
, whereas I want this query near the bottom to be the one that DBI sends because it is the one I wrote more recently:
SELECT a.DATE
,a.TRANSACTION_NUMBER
,b.FIRST_NAME
,b.LAST_NAME
FROM mydatabase.transactions a
INNER JOIN mydatabase.customerinfo b
ON a.ID = b.ID;