0

There are quite a few questions which cover this error, but I'm having the issue that:

A) I'm currently doing a Merge (with an Insert)

B) I'm not explicitly setting the identity column!

My stored procedure (table names and properties obfuscated):

Table Definition

CREATE TABLE MyTable (
    [ID] BIGINT IDENTITY(1, 1) NOT NULL,
    [Value] NVARCHAR (256) NOT NULL,
    [Property] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [IX_MyTable] UNIQUE NONCLUSTERED ([Value] ASC)
);

Stored Procedure Definition

CREATE TYPE TempType as Table (
    [TempValue] nvarchar(256) NOT NULL,
    [TempProperty] nvarchar(256) NOT NULL,
);
GO

CREATE PROCEDURE [Name]
    @Source TempType READONLY
AS
BEGIN
    MERGE [MyTable] as target
    USING (SELECT * FROM @Source) as source (TempValue, TempProperty)
    ON (target.Value = source.TempValue)
    WHEN MATCH THEN
        UPDATE SET Value = source.TempValue, Property = source.TempProperty
    WHEN NOT MATCHED THEN
        INSERT ([Value], [Property])
        VALUES (source.TempValue, source.TempProperty)
        OUTPUT deleted.*, $action, inserted.* INTO [MyTable];

END

From what I can see, I'm not anywhere explicitly specifying the IDENTITY column. I am specifying a UNIQUE-ly constrained column though.

Lastly, contrary to what the error says, specifying IDENTITY_INSERT ON does nothing.

Edit: I should also specify, I'm getting this error while deploying from a .dacpac via C#.

Nate Diamond
  • 5,525
  • 2
  • 31
  • 57
  • 2
    `OUTPUT .... INTO [MyTable]` does seems like inserting into an identity column to me – Lamak Oct 26 '15 at 16:46
  • It's inserting a new row and letting the table handle it though right? I'm able to insert into the table directly (not in a merge) elsewhere just fine. I should also clarify that I'm not sure what that output section is doing. That I copied from one of MSFTs examples and the `MERGE` doesn't seem to work without it. Is there a way to remove it or remove the insert? – Nate Diamond Oct 26 '15 at 16:48
  • 1
    It doesn't seem like you need the `OUTPUT` at all – Lamak Oct 26 '15 at 16:50
  • Ah, I think I see. I can just remove the table (or the whole OUTPUT) from the OUTPUT declaration and it works! I had been removing OUTPUT but not removing 'INTO MyTable' when testing before. If you want to add as an answer I'll accept it. Thanks so much! – Nate Diamond Oct 26 '15 at 16:50
  • It wasn't really an answer though, just an educated guess – Lamak Oct 26 '15 at 16:51
  • It's an answer in that it highlighted the cause of the error, which was evading me. "The OUTPUT clause is inserting into your table and hitting the identity column, causing the issue." – Nate Diamond Oct 26 '15 at 16:53

1 Answers1

0

I would simply get rid of the MERGE statement (has lots of issues by design) and use a simple insert statement as follows:

CREATE PROCEDURE [Name]
    @Source TempType READONLY
AS
BEGIN
  SET NOCOUNT ON;

    INSERT INTO [MyTable] ([Value], [Property])
    SELECT s.TempValue, s.TempProperty 
    FROM @Source s
    WHERE EXISTS (SELECT 1 
                  FROM [MyTable]  
                  WHERE Value = s.TempValue)

END

To read about issues with MERGE statement have a look at this article Use Caution with SQL Server's MERGE Statement

Since you have added an update into your Merge statement, now I would also add an update statement and wrap the update and insert into one transaction but still I would not recommend using merge statement. 

CREATE PROCEDURE [Name]
    @Source TempType READONLY
AS
BEGIN
  SET NOCOUNT ON;


BEGIN TRANSACTION;

     UPDATE T
       SET T.[Property] = S.[Property]
     FROM [MyTable] T 
     INNER JOIN @Source s ON T.Value = s.TempValue



    INSERT INTO [MyTable] ([Value], [Property])
    SELECT s.TempValue, s.TempProperty 
    FROM @Source s
    WHERE NOT EXISTS (SELECT 1 
                      FROM [MyTable]  
                      WHERE Value = s.TempValue)


COMMIT TRANSACTION;

END
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Wouldn't I need to do `INSERT` and `UPDATE` as well? – Nate Diamond Oct 26 '15 at 17:09
  • @NateDiamond You aren't actually updating anything though. And you are inserting the rows when they do match your target table, so that's odd too – Lamak Oct 26 '15 at 17:12
  • @NateDiamond As Lamak has already mentioned your Merge statement is only inserting when there is a matching row based on the criteria you have defined, there is no update at all in your merge statement, On a side note if you need and update, add a separate update statement to above solution and warp them both in ONE TRANSACTION, still I would stay away from Merge statement. – M.Ali Oct 26 '15 at 17:15
  • Whoops! Somehow the update section wasn't included in the post. Fixed! What's worse is I've already had my coffee :| – Nate Diamond Oct 26 '15 at 17:21
  • @NateDiamond have a look at my solution now, like I said before I would stay away from Merge statement. – M.Ali Oct 26 '15 at 17:27