SQL Server 中的 R:将数据框输出到表中 [英] R in SQL Server: Output data frame into a table
问题描述
这可能有一个简单的答案,但我无法弄清楚,因为我仍然掌握在 SQL Server 中使用 R 的窍门.我有一段代码从 SQL Server 表中读取数据,在 R 中执行并返回一个数据框.
This probably has a simple answer but I cannot figure it out as I'm still getting a hang of working with R in SQL Server. I have a piece of code that reads in data from a SQL Server table, executes in R and returns a data frame.
execute sp_execute_external_script
@language=N'R',
@script=N'inp_dat=InputDataSet
inp_dat$NewCol=max(inp_dat$col1,inp_dat$col2)
new_dat=inp_dat
OutputDataSet=new_dat'
@input_data_1=N'select * from IM_COMP_TEST_SQL2016.dbo.temp_table';
我想将 new_dat 插入到 SQL Server 表中(从 new_dat 选择 * 到 new_table).我该怎么做?提前致谢.
I want to insert new_dat into a SQL Server table (select * into new_table from new_dat). How do I go about this? Thanks in advance.
推荐答案
如图所示 教程,您可以在之前创建的列对齐的表中使用 INSERT INTO ... EXEC
脚本的数据帧返回:
As shown in this tutorial, you can use INSERT INTO ... EXEC
in a previously created table with columns aligning to script's dataframe return:
INSERT INTO Table1
execute sp_execute_external_script
@language=N'R',
@script=N'inp_dat <- InputDataSet
inp_dat$NewCol <- max(inp_dat$col1,inp_dat$col2)
new_dat <- inp_dat',
@input_data_1=N'SELECT * FROM IM_COMP_TEST_SQL2016.dbo.temp_table',
@output_data_1=N'newdat';
但是,要使用生成表查询可能需要 OPENQUERY()
或 OPENROWSET()
使用此 SO Post 返回存储过程的输出:
However, to use the make-table query may require OPENQUERY()
or OPENROWSET()
using an ad-hoc distributed query as described in this SO Post to return the output of stored procedure:
存储过程
CREATE PROCEDURE dbo.R_DataFrame
AS
BEGIN
execute sp_execute_external_script
@language=N'R',
@script=N'inp_dat <- InputDataSet
inp_dat$NewCol <- max(inp_dat$col1,inp_dat$col2)
new_dat <- inp_dat',
@input_data_1=N'SELECT * FROM IM_COMP_TEST_SQL2016.dbo.temp_table',
@output_data_1=N'newdat';
-- ADD ALL COLUMN TYPES;
WITH RESULT SETS (("newdat" [col1] varchar(20), [col2] double, [col3] int ...));
END
GO
操作查询
SELECT * INTO Table1
FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
'EXEC dbo.R_DataFrame')
这篇关于SQL Server 中的 R:将数据框输出到表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!