inserting/updating data on table from a stored procedure result set

Question

I've a stored procedure called proc_item that fetches data from different tables (using join). I want the result set from the stored procedure to be either inserted (if the data is new) or updated (if the data already exists) on another table called Item. Can anybody give me some idea as to how i can do this? I'm new to sql, stored procedures and looping.

thanks

If you are loooping through records to do any insert/update or delte you are doing it wrong. You should use looping as a technique of last resort not as a first choice. — HLGEM, Jun 01 '11 at 19:04

Solomon Rutzky · Answer 1 · 2012-07-28T21:25:36.697

You should create a Temporary Table to hold the results of the Stored Proc and then merge the results into your table. A Temporary Table is recommended over a Table Variable as it will JOIN better to the existing Table due to better statistics.

CREATE TABLE #TempResults
(
  Field1 DATATYPE1,
  Field2 DATATYPE2,
  ...,
  PRIMARY KEY CLUSTERED (KeyField1,...)
)

INSERT INTO #TempResults (Field1, Field2, ...)
   EXEC Schema.ProcName @Param1, ...

Now, there are two ways to do the merge. The first works in all versions of SQL Server and the second uses a command that was introduced in SQL Server 2008.

-- this should work on all SQL SERVER versions
UPDATE  rt
SET     rt.Field2 = tmp.Field2,
        ...
FROM    Schema.RealTable rt
INNER JOIN   #TempResults tmp
        ON   tmp.KeyField1 = rt.KeyField1
        ...

INSERT INTO Schema.RealTable (Field1, Field2, ...)
   SELECT  tmp.Field1, tmp.Field2, ...
   FROM    #TempResults tmp
   LEFT JOIN  Schema.RealTable rt
          ON  rt.KeyField1 = tmp.KeyField1
          ...
   WHERE   rt.KeyField1 IS NULL

OR:

-- the MERGE command was introduced in SQL SERVER 2008
MERGE Schema.RealTable AS target
USING (SELECT Field1, Field2,... FROM #TempResults) AS source (Field1, Field2,..)
ON (target.KeyField1 = source.KeyField1)
WHEN MATCHED THEN 
  UPDATE SET Field2 = source.Field2,
         ...
WHEN NOT MATCHED THEN   
  INSERT (Field1, Field2,...)
     SELECT  tmp.Field1, tmp.Field2, ...
     FROM    #TempResults tmp

For more information on the MERGE command, go here:
http://msdn.microsoft.com/en-us/library/bb510625(v=SQL.100).aspx

Now, if you have a large result set to merge and the table you are merging into is very large and has a lot of activity on it where this type of operation might cause some blocking, then it can be looped to do sets of 1000 rows at a time or something like that. Something along the lines of this:

<insert CREATE TABLE / INSERT...EXEC block>

CREATE TABLE #CurrentBatch
(
  Field1 DATATYPE1,
  Field2 DATATYPE2,
  ...
)

DECLARE @BatchSize SMALLINT = ????

WHILE (1 = 1)
BEGIN

    -- grab a set to work on
    DELETE TOP (@BatchSize)
    OUTPUT deleted.Field1, deleted.Field2, ...
    INTO #CurrentBatch (Field1, Field2, ...)
    FROM #TempResults

    IF (@@ROWCOUNT = 0)
    BEGIN
        -- no more rows
        BREAK
    END

    <insert either UPDATE / INSERT...SELECT block or MERGE block from above
     AND change references to #TempResults to be #CurrentBatch>

    TRUNCATE TABLE #CurrentBatch

END

This is a far better answer than the accepted one which shows a poor technique to get to the same answer. — HLGEM, Jun 01 '11 at 19:03
srutzky, thank you so much for your answer on this post. It has been very helpful. I would like to know, would you mind showing the necessary code for looping through 1k rows at a time. I plan to use this operation on a table containing >68k rows. Thank You. — Josh McKearin, Jul 27 '12 at 17:44
@jpm0004, you are welcome and I have added the example at the bottom of the answer. — Solomon Rutzky, Jul 28 '12 at 21:31
@srutzky Thank You! Just an interesting observation. I applied the original procedure to my situation (without the looping) and the stored procedure does its thing comparing two tables with over 68k rows each and making updates in about 4 seconds. Not too shabby. — Josh McKearin, Jul 29 '12 at 01:45

score 2 · Accepted Answer · edited May 23 '17 at 10:31

Take a look at @srutzky 's solution which is more appropriate to this problem

The first thing you could do is write everything into a table. To do so, you need to define a table, which has the same columns as they are returned by your stored procedure:

DECLARE @myTempTableName TABLE(
                                dataRow1 DATATYPE,
                                dataRow2 DATATYPE,
                                ...
                               )

INSERT INTO @myTempTableName(dataRow1, dataRow2,...)
EXEC( *mystoredprocedure* )

Now all the data you need is in the table. Next step is to check what you need to update and what to insert. Let's say datarow1 is the variable to check if it already exists or not (for example: same name or same id) AND let's say it's unique (else you need also something witch is unique - needed for iterating through the temporary table)

DECLARE @rows INT,
        @dataRow1 DATATYPE,
        @dataRow2 DATATYPE, ...

-- COUNT Nr. of rows (how many rows are in the table)
SELECT 
    @rows = COUNT(1) 
FROM 
    @myTempTableName 

-- Loop while there are still some rows in the temporary table
WHILE (@rows > 0)

BEGIN

  -- select the first row and use dataRow1 as indicator which row it is. If dataRow1 is not unique the index should be provided by the stored procedure as an additional column
  SELECT TOP 1
    @dataRow1 = dataRow1,
    @dataRow2 = dataRow2, ..
  FROM
    @myTempTableName

  -- check if the value you'd like to insert already exists, if yes --> update, else --> insert
  IF EXISTS (SELECT * FROM *TableNameToInsertOrUpdateValues* WHERE dataRow1=@dataRow1)
      UPDATE 
            *TableNameToInsertOrUpdateValues*
      SET
            dataRow2=@dataRow2
      WHERE
            dataRow1=@dataRow1

  ELSE
      INSERT INTO 
            *TableNameToInsertOrUpdateValues* (dataRow1, dataRow2)
      VALUES 
            (@dataRow1, @dataRow2)


  --IMPORTANT: delete the line you just worked on from the temporary table
  DELETE FROM 
     @myTempTableName 
  WHERE 
     dataRow1= @dataRow1

  SELECT 
     @rows = COUNT(1) 
  FROM 
     @myTempTableName

END -- end of while-loop

The declaration can be done, at the beginning of this Query. I put it on the place where I used it so that it's easier to read.

Where I got part of my Code from and also helpful for iterating through tables (solution from @cmsjr without cursor): Cursor inside cursor

I'm sorry to disappoint you. I'm still new to SQL - so I'm not surprised that my Answer isn't the best way to do it and shouldn't even be used like this... still I'm trying to learn and improve ... — skofgar, Jun 01 '11 at 21:37
It concerned me most that it was the accepted answer and that many others searching might also be led to the less than optimal solution (it does work but it is not the best way to solve a database problem). @skofgar, you might want to check out this article to help you learn better ways to solve these types of prblems: http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them — HLGEM, Jun 01 '11 at 22:11
I wouldn't go that far. You added something to look at the other solution. — HLGEM, Jun 02 '11 at 13:23

score 1 · Answer 3 · edited May 23 '17 at 12:17

1

You need first to insert the data into a temporary container, like a temporary table or a table variable. Then you can work with the table as usual: join it, derive result sets from it etc.

Check this question for the options for storing the output of an SP into a table.

edited May 23 '17 at 12:17

Community

1
1

answered Jun 01 '11 at 08:56

Andriy M

76,112
17
94
154

inserting/updating data on table from a stored procedure result set

3 Answers3

Take a look at @srutzky 's solution which is more appropriate to this problem

Linked