3

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.

Community
  • 1
  • 1
Tim
  • 31
  • 1
  • 3
  • A similar question was asked [here](http://stackoverflow.com/questions/4747768/rodbc-temporary-table-issue-when-connecting-to-ms-sql-server/4748281#4748281) that may be of interest to you. – Chase Jul 26 '11 at 12:52

1 Answers1

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.

Nicolas78
  • 5,124
  • 1
  • 23
  • 41
  • 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. – Tim Jul 26 '11 at 12:06
  • 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 Jul 26 '11 at 12:25
  • "which of the many queries..." it should be in the last sentence ;) – Nicolas78 Jul 26 '11 at 13:50