Skip to main content
Dec 27, 2022 12:18 PM UTC
Solved

Using OUTPUT in SQL Command

Hello,

 

I wanted to use the "OUTPUT" clause in an SQL command to get the latest inserted row.

However I am getting this error: 

Execute Update failed: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.

 

Now, the SQL that i am using does work in SSMS. Here is the structure:

 

 

 

INSERT INTO Table
(value1, value2)
OUTPUT Inserted.uid
values(1,2)

 

Any ideas?

 

I would if possible like to avoid using stored procedures.

 

Thanks,

Jan 3, 2023 5:00 AM UTC

Use service type as "Query" as following,

Sathishkumar_C_0-1672721873201.png

 

4 replies

Jan 2, 2023 7:26 PM UTC

Try using a table variable to capture the identity column value for the newly inserted row:

declare @uid int;
declare @tableOut table(uid int);

insert into SOMETABLE (
FIRST_NAME,
LAST_NAME output
INSERTED.UID into @tableOut values (
'Neil',
'Peart'
); select @uid = uid from @tableOut;

 

hope that helps,

dgg

Jan 3, 2023 5:00 AM UTC

Use service type as "Query" as following,

Sathishkumar_C_0-1672721873201.png

 

jenscAuthor
Jan 3, 2023 7:03 AM UTC

Hello,

 

I was under the assumption that insert/update/delete only worked using the "command" type of SQL on TWX.

This is exactly what I was looking for!

 

Thanks,

Jens