R：从文本文件运行多行sql
在R中，如何将多行文本文件（包含SQL）的内容导入到多行SQL中？
我研究过 将多行 SQL 查询导入到单个字符串 并设法让一个简单的 sql 脚本正常工作。但是，当 SQL 需要换行时（即，当您添加 SELECT 语句时），当您将 sql 脚本的所有行放在一行中时，它不起作用。
sql .txt 文件如下所示：
CREATE TABLE #Countries (Country varchar(255), Region varchar(255))
INSERT INTO #Countries VALUES ('China', 'EM')
SELECT * FROM #Countries
R 代码如下所示：
fileconn<-file("R/sql.txt","r")
sqlString<-readLines(fileconn)
sqlString<-paste(sqlString,collapse="","")
sqlconn <- odbcDriverConnect(connection = ....)
sqlQuery(sqlconn,sqlString)
我也尝试过 CAT 和 GSUB，但我感觉当第三条语句在一行中的第二条语句后面时，就会出现问题。
谁能帮我解决这个问题吗？非常感谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
有两种分离 SQL 命令的方法。要么你单独发送。这就是通过在 for 循环中执行文件的每一行所得到的结果，但是当然，您会遇到一个问题，如果单个命令确实需要多行命令，该怎么办，对吗？分隔 SQL 命令的第二种方法是简单地以 ; 结束它们。如果将其放在每个命令的末尾，那么您应该能够在单个字符串中将任意数量的命令传递给数据库。
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.