I recently discovered that I could create SQL scripts in RStudio and execute them and see the results in the SQL Results window in RStudio. Pretty slick!
RStudio determines which connection to run the SQL on by the header comment in the SQL text:
Where is this documented? I'm wondering what the !preview means. Are there other options? Can I use rstudioapi::askForPassword("Enter your pw") in that somehow? It seems like all these things should be in the documentation, but I can't find any documentation around SQL files in RStudio. Any help is appreciated!
Those are great resources. I had seen those, but was more interested in documentation specific to editing and previewing (running) SQL scripts in R Studio. None of the links seem to specifically address that, unless I'm missing something obvious (would not be the first time).
Are you talking about "sql code chunks" when you say "sql scripts" in RStudio? Info about that is in in the first link above section 2.7.3. That's Section "2.7.3 SQL from Yihui Xie's new book "R Markdown."
I often copy and paste SQL scripts from Microsoft SQL Server Management Studio directly into RStudio code chucks, but I could directly edit the SQL in RStudio, too. You can run an SQL code chunk just like any other R code chunk.
I enclose all my SQL chunks between dbConnect and dbDisconnect statements. The variable assigned from the dbConnect is the one you pass to the connection parameter in the sql code chunk. The output.var in the sql chunk receives the results from the SQL query that you can manipulate in R.
Nope. Those are R Markdown files. I'm talking about plain text files containing SQL only. You can get an example by selecting File -> New File -> SQL Script:
Notice the -- !preview SQL comment at the top? That's a comment to SQL but RStudio is clearly parsing it and using it for connection information. I'm looking for the documentation around that specific feature.
I think I understand what you're saying, but I see no "SQL script" menu option like you're showing in the two PCs I'm using with RStudio installed in the last week or so.
interesting.. it's been in the source tree for ~4 months. I didn't realize it's not in production stable yet. I'm running the nightly build but I noticed that rstudio.cloud has the SQL Script feature.
I believe it’s in the Preview release, but not the current regular release (I can’t verify from where I am right now, but a headline feature of 1.2 is “author, run and preview SQL query results”)?
May I politely suggest that it would behoove everybody in conversations like this to include their RStudio version info, since there’s a fair bit of confusion about what people mean by “current RStudio”: latest stable patch? Preview? Dailies? The feature sets can diverge pretty widely.
Ha. The IDE team was watching this conversation and Javier stepped up to put together this documentation.
Just a word of caution, this feature is still under development and so there is a chance it will change. If you run into issues, Comment Headers in RStudio Sources will be a good place to see the latest documentation.
Oh sorry javier !
When I stumbled upon the article, I immediately thought about this post.
It did not cross my mind that the documentation was made due to this post. I thought it was just perfect coincidence as there was no comment in the post here (should have known this doesn't exist ), so I did not want to forget about sharing the link, and I did.
thanks y'all! I was afraid I was just unable to find the documents because there's so much search noise around "sql" when searching online.
This is a neat feature and I really appreciate the docs! It looked like a feature that has many applications. I had not realized how very new this feature is. I just thought I hadn't noticed it before
I thought it was just perfect coincidence as there was no comment in the post here (should have known this doesn't exist 
), so I did not want to forget about sharing the link, and I did.