使用rstudio中的SQL代码在SQL Server中创建表
我的SQL代码如下，但是有更多列，并且在SQL脚本中的此表上也创建了一个非集群索引。
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ModelOutput)
BEGIN
CREATE TABLE ModelOutput
(
[OutputID] [bigint] IDENTITY(1,1) NOT NULL,
[Prediction] [int] NOT NULL,
[CreateDate] [datetime] DEFAULT CONVERT(DATETIME2(0), getdate()),
) ON [PRIMARY]
END;
此代码保存在一个名为createTable.sql的文件中，我想使用rstudio运行。
当前，我的R代码看起来像：
#Read SQL file
sqlcode = read_file(file = "CreateTable.sql")
#Run SQL code
DBI::dbSendQuery(conn = con, statement = sqlcode)
但是，当我试图弄清为什么它似乎没有运行时，我发现dbsendquery（）仅与Select语句一起使用（
我环顾四周，找不到任何方法来运行Rstudio的Create Table SQL语句。我有什么选择，还是我需要完全重新考虑我的过程以使用dbCreateTable（）？
I have SQL code like below, but with more columns and I also create a nonclustered index on this table within the sql script.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ModelOutput)
BEGIN
CREATE TABLE ModelOutput
(
[OutputID] [bigint] IDENTITY(1,1) NOT NULL,
[Prediction] [int] NOT NULL,
[CreateDate] [datetime] DEFAULT CONVERT(DATETIME2(0), getdate()),
) ON [PRIMARY]
END;
This code is saved in a file called CreateTable.sql which I want to run using RStudio.
Currently, my R code looks like:
#Read SQL file
sqlcode = read_file(file = "CreateTable.sql")
#Run SQL code
DBI::dbSendQuery(conn = con, statement = sqlcode)
However, when I tried to figure out why it didn't seem to be running, I found that dbSendQuery() only works with select statements (https://cran.r-project.org/web/packages/DBI/vignettes/DBI-advanced.html). So then I found dbCreateTable() but that only works if I have created the table using R. I don't want to do that since I can't set the same column defaults or create an index--as far as I know.
I have looked around and cannot find any way to run a create table SQL statement from RStudio. Are there any options for me or do I need to rethink my process entirely to just use dbCreateTable()?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我尝试了许多选项，并找到了从rstudio connect运行任何SQL代码的最可靠方法，对我来说，Rstudio Server Pro是将其转换为过程。然后运行
dbi :: dbsendquery（con，“在\ nexec yourprocedure上设置nocount”）I tried many options and found the most reliable way to run any SQL code from RStudio Connect and RStudio Server Pro for me was to turn it into a procedure. Then run
DBI::dbSendQuery(con, "SET NOCOUNT ON\nEXEC YourProcedure")