Possible to combine Table-Value-Parms With MERGE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Possible to combine Table-Value-Parms With MERGE?

Author

Topic

TheKai
Starting Member

16 Posts

Posted - 2010-02-12 : 00:05:34

Howdy folks. Thanks for taking a look at my question.

I have a table called Stats:

CREATE TABLE [dbo].[tblStats](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OwnerID] [int] NOT NULL,
[StatID] [int] NOT NULL,
[StatValue] [int] NOT NULL

I need to update multiple rows in this table for a given OwnerID at a time. If a particular stat doesn't currently exist, I need to insert it. Currently, I'm doing this by iterating over all stat rows on the client and executing the following proc for each stat that needs to be updated/inserted.

CREATE PROCEDURE dbo.UpdateOrInsertStats
@OwnerID int,
@StatID int,
@StatValue int
AS
BEGIN
SET NOCOUNT ON;
-- Update the row if it exists.
UPDATE tblStats
SET StatValue = @StatValue
WHERE OwnerID = @OwnerID AND StatID = @StatID
-- Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO tblStats (OwnerID , StatID, StatValue)
VALUES (@OwnerID , @StatID, @StatValue)
END
END;
GO

This is slower than I can allow. I understand that by using TVPs, I might be able to insert multiple rows at a time, but that's not quite enough for my scenario.

My question is therefore: Is it possible by combining the use of TVPs with the new MERGE command (or maybe without) to pass a table of OwnerID, StatID, StatValue records to a proc and have it update and insert as necessary without me having to issue seperate proc calls for each row in question?

Thanks in advance for any help. It would really help me out!

-Kai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 02:09:23

Its possible

See below for mockup of your scenario and solution using TVPs and MERGE

first create a table type for use by TVP


CREATE TYPE [dbo].[tblStats] AS TABLE
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[OwnerID] [int] NOT NULL,
[StatID] [int] NOT NULL,
[StatValue] [int] NOT NULL
)

Now create your procedure based on this


CREATE PROCEDURE dbo.UpdateOrInsertStats
@InputTable tblStats READONLY
AS 
BEGIN
SET NOCOUNT ON; 
MERGE DestinationTable AS t
USING @InputTable AS s
ON s.OwnerID=t.OwnerID
AND s.StatID=t.StatID
WHEN MATCHED THEN UPDATE SET  t.StatValue=s.StatValue
WHEN NOT MATCHED THEN INSERT (OwnerID,StatID,StatValue) VALUES (s.OwnerID,s.StatID,s.StatValue);


END;
GO


Now lets test the procedure with some sample data 

--Your destination table created with some test values

CREATE TABLE DestinationTable
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[OwnerID] [int] NOT NULL,
[StatID] [int] NOT NULL,
[StatValue] [int] NOT NULL
)
GO
INSERT INTO DestinationTable
VALUES (1022,121,300),(1025,113,430),(1028,118,332),(1014,129,123),(1022,231,456)
SELECT * FROM DestinationTable

output
--------------------------------

ID	OwnerID	StatID	StatValue
1	1022	121	300
2	1025	113	430
3	1028	118	332
4	1014	129	123
5	1022	231	456


Now lets create an input table

DECLARE @InpTable tblStats
INSERT INTO @InpTable
VALUES (1022,121,344),(1031,114,324),(1028,118,400),(1009,110,566),(1022,231,332)
SELECT * FROM @InpTable

output
--------------------------------
ID	OwnerID	StatID	StatValue
1	1022	121	344
2	1031	114	324
3	1028	118	400
4	1009	110	566
5	1022	231	332


please note i've included 3 already existing values pairs with different statvalues as well as two new pairs

so once we do merge we get two new rows in destination and three others would have updated stat value

now lets call procedure


DECLARE @InpTable tblStats
INSERT INTO @InpTable
VALUES (1022,121,344),(1031,114,324),(1028,118,400),(1009,110,566),(1022,231,332)

SELECT * FROM DestinationTable
EXEC UpdateOrInsertStats @InpTable
SELECT * FROM DestinationTable



output
-----------------------------
before merge
ID	OwnerID	StatID	StatValue
1	1022	121	300
2	1025	113	430
3	1028	118	332
4	1014	129	123
5	1022	231	456


after merge

1	1022	121	344
2	1025	113	430
3	1028	118	400
4	1014	129	123
5	1022	231	332
6	1031	114	324
7	1009	110	566

the ones in blue have updated values and last two rows have been newly inserted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TheKai
Starting Member

16 Posts

Posted - 2010-02-12 : 12:52:53

Thanks a ton! That was the most helpful and complete response I've gotten on the internet in years! :) I really appreciate you taking the time to help me with this. It's exactly what I was looking for!

-Kai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:57:26

you're welcome

Feel free to post when you're in doubt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources