Using SQL Server MERGE statement

Question

I have 3 related tables:

And I need to create a stored procedure to save new orders. I follow to existing practicies in project i'am working on and here is my sql code, first create custom type:

-- create custom type that will be used later as SP parameter type
CREATE TYPE [dbo].[Admin_Batch_TOrderTable] AS TABLE
(
    [groupKey] varchar(100) NOT NULL,
    [key] varchar(100) NOT NULL,
    [value] nvarchar(MAX) NULL
)
GO

Then create the stored procedure itself to save new orders:

CREATE PROCEDURE [dbo].[Admin_Batch_Order_Save]
    @arenaId smallint,
    @table AS [dbo].[Admin_Batch_TOrderTable] READONLY,
    @author nvarchar(100) = NULL
AS
    MERGE [dbo].[OrderGroup] AS g -- TARGET
        USING (SELECT DISTINCT [groupKey] FROM @table) AS t -- SOURCE
        ON g.[key] = t.[groupKey] AND g.[arenaId] = @arenaId
    WHEN NOT MATCHED BY TARGET THEN
        INSERT ([arenaId], [key], [author]) VALUES (@arenaId, t.[groupKey], @author);

        DECLARE @output TABLE ([action] varchar(20), [id] int)

RETURN 0

So, when the stored procedure will be called, it will first try to add data to the [dbo].[OrderGroup] table using a MERGE statement. It almost works, but it should be like

INSERT ([arenaId], [typeId] [key], [author]) 
VALUES (@arenaId, "HOW TO GET TYPEID HERE?" t.[groupKey], @author);

I can't figure out how I could get [typeId] value, I try to use nested SELECT statement just inside INSERT, but it seems it is not allowed.

Any help will be appreciated. Thanks.

Answer 1

You have either to create an entry in the OrderTypes too or have to pass the TypeId as parameter. SQL Server can't just guess an ID for you.

Btw: the MERGE statement is nice but there are several problems and restrictions with it. Better use an INSERT INTO ... SELECT FROM ... WHERE NOT EXISTS (or better do it with two statements and check the nonexistence first and insert only if COUNT(*) = 0.