T-SQL UPSERT table with multiple values from TVP

Question

I use SQL Server 2012, and T-SQL as query language.

I need help updating/inserting multiple columns in [cross_function_user] using one ID value passed as a parameter (@userGroupID) and lots of function id's. They are List in C#, and passed to the sproc as Table Valued Parameter - with just one column of int, named Item.

ALTER PROCEDURE [whatever]
    @userGroupID INT,
    @listID AS IntList READONLY
AS
BEGIN   
        SET NOCOUNT ON;
    MERGE INTO [dbo].[cross_function_user] USING @listID
    ON [dbo].[cross_function_user].id_group_user = @userGroupID
    WHEN MATCHED THEN
        UPDATE SET [cross_function_user].id_group_user = @userGroupID,
                   [cross_function_user].id_function = (SELECT Item FROM @listID)
    WHEN NOT MATCHED THEN
        INSERT (id_group_user, id_function) 
        VALUES (@userGroupID, (SELECT Item FROM @listID) );

END

First, it errors 'subquery returned more than one result' of course, but I lack skill to rewrite this, and I'm not really sure if my upsert is written right way. Any help would be highly appreciated.

Is there only one column in IntList? What is it called? You need to refer to a column in your MERGE statement. In this example you can see that you should alias your TVP and refer to the columns in it http://sqlblogcasts.com/blogs/simons/archive/2008/06/17/SQL-Server-2008---Killer-features---MERGE-and-Table-Valued-Parameters.aspx — Nick.Mc, Feb 01 '13 at 06:20
Thank you, ElectriLlama, article in the link helped to work it out perfectly! — Dr.Strangelove, Feb 06 '13 at 22:49

score 2 · Answer 1 · answered Feb 01 '13 at 06:23

Try this:

You need to replace YourColumn with the name of the column in your TVP.

ALTER PROCEDURE [whatever]
@userGroupID INT,
@listID AS IntList READONLY
AS
BEGIN   
    SET NOCOUNT ON;
    MERGE INTO [dbo].[cross_function_user] USING @listID
    ON [dbo].[cross_function_user].id_group_user = @userGroupID
    WHEN MATCHED THEN
        UPDATE SET [cross_function_user].id_function = S.YourColumn
    WHEN NOT MATCHED THEN
        INSERT (id_group_user, id_function) 
        VALUES (@userGroupID, S.YourColumn);

END

T-SQL UPSERT table with multiple values from TVP

1 Answers1