R: run multiple line sql from text file

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

I've st开发者_JS百科udied 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.

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.