R: run multiple line sql from text file

Question

In R, how can I import the contents of a multiline text file (containing SQL) into multiple lines of SQL?

I've studied Import multiline SQL query to single string and managed to get a simple sql script working. However, when SQL demands a new line (ie when you add a SELECT statement) it doesn't work when you put all lines of sql script in one line.

The sql .txt file looks like:

CREATE TABLE #Countries (Country varchar(255), Region varchar(255)) 
INSERT INTO #Countries  VALUES ('China', 'EM') 
SELECT * FROM #Countries

The R code looks like:

fileconn<-file("R/sql.txt","r")            
sqlString<-readLines(fileconn)           
sqlString<-paste(sqlString,collapse="","") 
sqlconn <- odbcDriverConnect(connection = ....)
sqlQuery(sqlconn,sqlString)

I've tried CAT and GSUB as well, but I've got the feeling that the problem occors when the third statement follows the second in one line.

Can anyone help me with this problem? Many thanks.

A similar question was asked here that may be of interest to you. — Chase
– Chase, Commented Jul 26, 2011 at 12:52

Nicolas78 · Accepted Answer · 2011-07-26 11:12:57Z

3

There are two ways of separating SQL commands. Either you send them separately. This is what you would get by just executing each line of the file in a for loop, but then of course you got the problem what to do if a single command does require more than one line, right? The second way to separate SQL commands is simply to end them with a ;. If you put that at the end of each command, you should be able to pass as many of them as you like to the DB in a single string.

answered Jul 26, 2011 at 11:12

Nicolas78

5,1441 gold badge26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Over a year ago

Nicloas, many thanks for your quick response. Uploading all lines with semicolons works. The only issue still is that sqlQuery returns the first result set (adding one row of data to the temp table - which is empty) instead of the select statement (which should return China/EM). Is there a way to get the last result set from the uploaded string? Thanks.

Nicolas78 Over a year ago

No idea, seems very implementation-specific. I think I'd just send the last query separately. This may seem like redundant code, but it's actually not. You separate the building and the querying code and make that explicit. Whether this second query is an extra file, a hard-coded query, or just always the last line, pick what fits your scenario. Anyone who'll read your code (including future you) will be happy to see which results you want without having to wonder "which many queries creates the results"?

Nicolas78 Over a year ago

"which of the many queries..." it should be in the last sentence ;)

Collectives™ on Stack Overflow

R: run multiple line sql from text file

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related