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

Answer 1

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