0

I am attempting to get the OUTPUT table from an INSERT command on a SQL Server database (v14).

 DECLARE @i TABLE (TICKET_ID int, CREATED_DATE datetime);

 INSERT INTO dbo.TICKET (SITE_ID, TICKET_TITLE, USER_ID) 
 OUTPUT INSERTED.TICKET_ID, INSERTED.CREATED_DATE INTO @i 
 VALUES (@s, @t, @q);

 SELECT * FROM @i;

I have 3 parameters that are being populated for the site, ticket title and user by @s, @t and @u respectively.

I am calling the command using a SqlClient.SqlDataReader Object with ExecuteReader Function on the 4.5 Framework.

The command successfully inserts the record into the database table, but does not return the table @i with the ticket ID (Auto Increment) and created date (default =GETDATE) which is what I am expected to present to the user once saved to the database.

I have played around with the removing of the @i TABLE in the command, but I get a .NET error:

The target table 'name' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

I have found a couple of links that provide some ways to fix these but it did not fix my issue

https://learn.microsoft.com/en-us/previous-versions/dotnet/articles/ms971497(v=msdn.10)?redirectedfrom=MSDN

Cannot use UPDATE with OUTPUT clause when a trigger is on the table

Is there any assistance that can be provided to assist in what I am trying to achieve?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Samuel
  • 17
  • 5

2 Answers2

0

I don't know why you'd need the table. I think you need to just execute this:

       INSERT INTO dbo.TICKET (SITE_ID, TICKET_TITLE, USER_ID) 
       OUTPUT inserted.TICKET_ID, inserted.CREATED_DATE
       VALUES (@s, @t, @q);

I can't see any value in the table. When you execute this statement, it'll return results to the client, just like a SELECT statement would.

Hope that helps.

Greg Low
  • 1,526
  • 1
  • 4
  • 4
  • If I remove references to the @i temporary table, I get the following message: `The target table 'dbo.TICKET' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.` – Samuel Jan 08 '20 at 07:05
  • Is there a trigger on the target table? What's it for? It'd be great if you don't need it. The inability to do it like that is a long term "bug" (at least in my mind) that I don't see being addressed any time soon. – Greg Low Jan 08 '20 at 07:14
  • So if you're stuck with the trigger, need to go back to your original query. Is there any chance it's coming back as multiple result sets? Have you tried doing a NextResultSet() call on the reader? (See if it's an extra result set) – Greg Low Jan 08 '20 at 07:18
  • 1
    Why do you need to use `OUTPUT`? Could you just do something like `select ticket_id, created_date from ticket where ticket_id = @@identity`? – iakobski Jan 08 '20 at 07:30
  • There appears to be trigger on this table on the INSERT that I wasn't aware of. So, I removed the OUTPUT clause from the command and then performed a SELECT using the @@identity. – Samuel Jan 08 '20 at 23:38
  • Also, don't use @@IDENTITY. Use SCOPE_IDENTITY() instead. SCOPE_IDENTITY() gives you the last identity value in your current scope. @@IDENTITY gives you the last identity value on your connection. So if there's an INSERT that you do, that triggers another INSERT (via a trigger), you'll get the 2nd IDENTITY value rather than the one that you want. SCOPE_IDENTITY() avoids that issue. – Greg Low Jan 09 '20 at 03:47
0

It seems a pain to have to but another option might be just to do this:

 INSERT INTO dbo.TICKET (SITE_ID, TICKET_TITLE, USER_ID) 
 VALUES (@s, @t, @q);

 SELECT TICKET_ID, CREATED_DATE 
 FROM dbo.TICKET 
 WHERE SITE_ID = @s AND TICKET_TITLE = @t AND USER_ID = @q;
Greg Low
  • 1,526
  • 1
  • 4
  • 4
  • I removed the OUTPUT clause and used the @@identity reference to perform a SELECT to get the table data I need. – Samuel Jan 08 '20 at 23:39