sql-server insert statement output clause doesnt work

Question

I did some research and found out that the best solution to get the id of newly inserted row is output clause.I am using the insert statement output clause for the first time and couldnt figure it out.

here is my sql code. what is wrong with this sql?

ALTER PROCEDURE [dbo].[MedBul_Insert_Message]      
                                              (@ParentID int, @MessageFrom varchar(500),@MessageTo varchar(500), @MessageSubject varchar(500),@MessageBody varchar(4000),@MessageIsRead int)
AS
BEGIN
    SET LANGUAGE Turkish;

Declare @message_id table (MessageID int);

insert into Messages output INSERTED.ID INTO @message_id (ParentID ,MessageFrom ,MessageSubject ,MessageBody ,MessageIsRead ) values(@ParentID ,@MessageFrom ,@MessageSubject ,@MessageBody ,@MessageIsRead );


END

"Doesn't work" - in what way? An error message? If so, what? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 19, 2013 at 7:25
it gives this error: Column name or number of supplied values does not match table definition. — Arif YILMAZ
– Arif YILMAZ, Commented Apr 19, 2013 at 7:26

Damien_The_Unbeliever · Accepted Answer · 2013-04-19 07:27:11Z

5

The OUTPUT clause should come after the column list:

insert into Messages (ParentID ,MessageFrom ,MessageSubject ,MessageBody ,MessageIsRead )
output INSERTED.ID INTO @message_id (MessageID)
values(@ParentID ,@MessageFrom ,@MessageSubject ,@MessageBody ,@MessageIsRead );

See the INSERT documentation.

(And also, obviously, you need to actually do something with the contents of @message_id now, but I presume you've not yet written or just not shown in your question, the remainder of the stored procedure)

answered Apr 19, 2013 at 7:27

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arif YILMAZ Over a year ago

yes, and I was going to ask you help me to insert that message_id into another table :)

Arif YILMAZ Over a year ago

I need to write another insert statement to insert that id, can you give me a short example for that?

Damien_The_Unbeliever Over a year ago

Work out what you want to insert into the next table as a SELECT statement, using @message_id as one of the tables in the FROM clause. You can then use INSERT (...) SELECT ... instead of INSERT (...) VALUES () to change that SELECT into an INSERT.

Arif YILMAZ Over a year ago

my second insert statement is easy but I dont know how to do it? here is the insert "insert into MessagesTo (MessageID,ToProfessionalID) values()"

Ace Hyzer Over a year ago

Technically you don't need to output into anything either, just having the output statement will return the values as expected. Here's a code example

Collectives™ on Stack Overflow

sql-server insert statement output clause doesnt work

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related