R in SQL Server: Output data frame into a table

Question

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';

I want to insert new_dat into a SQL Server table (select * into new_table from new_dat). How do I go about this?

Community · Accepted Answer · 2017-05-23 12:02:36Z

5

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';

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:

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

Action Query

SELECT * INTO Table1 
FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
                'EXEC dbo.R_DataFrame')

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Oct 4, 2016 at 2:28

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rookieJoe Over a year ago

While trying your first solution, to directly insert into a table, I run into the following error: Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'. Would you be aware as to what is causing this? The table being inserted into has the correct number of columns expected and I am not passing any parameters. Thanks.

Collectives™ on Stack Overflow

R in SQL Server: Output data frame into a table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related