SQL - Cannot print values from 'inserted' table

Question

I have heard that the 'inserted' table automatically stores the last inserted record of any table temporarily.I was able to get the values from this inserted table when using triggers but not manually as

insert into samantha (id , name) values (11,'has')
select *from inserted

I am getting the error message as

Invalid object name 'inserted'.

Can you please give the reason.Thanks in advance

I believe you are restricted to using them only in triggers. ["You can use these **temporary**, **memory-resident tables** to test the effects of certain data modifications and to set conditions for **DML trigger actions**"](https://msdn.microsoft.com/en-us/library/ms191300.aspx) — Radu Gheorghiu, Jul 29 '15 at 07:38
http://stackoverflow.com/questions/23251739/some-issue-with-logical-tables-deleted-inserted-in-procedure its a duplicate — Deep Kalra, Jul 29 '15 at 07:47
`inserted` and `deleted` are part of the implementation of SQL Server triggers and OUTPUT clause. Is there a reason why this question is tagged [tag:mysql]? — Damien_The_Unbeliever, Jul 29 '15 at 07:51
Is this about SQL Server or MySQL? Please remove the wrong tag — James Z, Jul 29 '15 at 07:51

score 4 · Accepted Answer · answered Jul 29 '15 at 07:47

4

There is inserted table, but it can be only used with output clause, with something like this:

insert into samantha (id , name) 
output inserted.*
values (11,'has')

If you have an identity field and you want the latest value from it, you can use SCOPE_IDENTITY() -function too:

insert into samantha (id , name) 
values (11,'has')

select SCOPE_IDENTITY()

answered Jul 29 '15 at 07:47

James Z

12,209
10
24
44

This is for SQL Server, it seem you have tagged also MySQL – James Z Jul 29 '15 at 07:50

score 0 · Answer 2 · answered Jul 29 '15 at 07:44

0

try use SELECT LAST_INSERT_ID() from samantha

answered Jul 29 '15 at 07:44

huzain07

1,051
2
8
17

This doesn't really look like a solution for what the OP asked.. how do you get the `name`? – Radu Gheorghiu Jul 29 '15 at 07:45
the OP asked how to get data from last inserted by trigger in updated MySql. From MySql 5.0 until newest, the trigger for get last inserted data is LAST_INSERT_ID(). that trigger is used to get id from last inserted data. – huzain07 Jul 29 '15 at 07:54
He is also trying to get the value inserted in the `name` column. How can he do that? – Radu Gheorghiu Jul 29 '15 at 07:54

SQL - Cannot print values from 'inserted' table

2 Answers2