In a SQL Server database I have an InputTable
whose data is given to a stored procedure which runs an R-script on the rows whose Flag=0
after running the script I want to set Flag=1
on the specific rows of InputTable
on which the script was executed.
Below is my code for the stored procedure, in this way when I update after the script using the line UPDATE InputTable SET Flag=1
- while the procedure is running and if there is new data inserted in the table, this sets the Flag=1
on the newly inserted data on which the R-script/stored procedure was not executed.
BEGIN
INSERT INTO OutputTable
EXECUTE sp_execute_external_script @language = N'R'
, @script = N'
<running some R code in here>
'
, @input_data_1 = N'SELECT * FROM InputTable WHERE Flag=''0'';'
UPDATE InputTable SET Flag=1
END;
How can I track the Id's of the rows on which the R-script was executed? Currently, I can only return an OutputDataSet which I am Inserting into another table. Can I use another variable to return Id's of OutputDataSet and update Flag=1
on the specific Ids.
The main idea is to somehow mark the rows that have already been computed using the R-script in the stored procedure.