Using SQL Server MERGE statement

Question

I have 3 related tables:

enter image description here

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.

Where does this TYPEID come from? It isn't in the code you posted. Not sure why you need to use MERGE here. This looks like a basic insert where not exists. You might also take a peek at this article about MERGE. http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ — Sean Lange, Apr 30 '15 at 13:55
I'am sorry. typid should be detected by typekey, the additional argument in SP that i miss. Thanks anyway, i will try to replace merge with insert. — igorGIS, Apr 30 '15 at 14:12

score 3 · Accepted Answer · edited Apr 30 '15 at 14:00

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.

Using SQL Server MERGE statement

1 Answers1