Using OUTPUT in SQL Command

[jensc]

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,

[dgg]

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

[Sathishkumar_C · Accepted solution]

Use service type as "Query" as following,

[jensc]

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

[jensc]

Hello,

While it gives me what I wanted, it is a bit more convoluted then I would have hoped.

Satish Kumars response works as well and is perhaps easier to understand.

Thanks, 

Jens