Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
footmaniac84
Starting Member
12 Posts |
 Posted - 2011-05-10 : 06:31:33
|
| Hello. I want to use the output clause to get de Ids of the rows that have just been inserted, updated or deleted.I have two question:1. When I use the output clause in an insert i get the id with "inserted.id", in a delete with "deleted.id". But in an update? "updated.id"? I have read in other site that you can take it with "deleted.id" too.2. The more important question. I have a procedure P1 that first make an insert with OUTPUT clause that store de ids in a table T and second make a select that returns the ids from the table T.My question is: What's happening if between the insert and the select in my procedure, another insert is maken in another procedure P2 or execution? Will the OUTPUT clause return the ids of the insert in my procedure P1 or the ids ofr the insert in the other procedure P2?Please note that P1 and P2 are two independent procedures that insert rows in same or different tables.Thank you in advance. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-10 : 07:19:42
|
| Think of an update operation as two logical operations: one delete and one insert. So you would have both deleted and inserted tables available. inserted table would have the updated values and deleted would have the original values.When you use the output clause, that operation is atomic - but if you save it into another table, and then try to select from the table, it very well could have data inserted by another query. If you can post the query where you are inserting and then selecting, people on this forum may be able to offer suggestions. |
 |
|
|
footmaniac84
Starting Member
12 Posts |
Posted - 2011-05-10 : 08:14:42
|
| Thank you very much about the update explanation.About the query, you can see here:GODECLARE @AffectedTableIds table(id int,name varchar(50),symbol varchar(50));INSERT INTO TBL_COUNTRY (Name,Simbol)OUTPUT INSERTED.Id, INSERTED.Name, INSERTED.Symbol INTO @AffectedTableIds --Display the result set of the table variable.SELECT id,name,symbol FROM @AffectedTableIds;GOMy question is: Can I be completely sure that in AffectedTableIds will be the Ids inserted in my INSERT clause?Thank you so much. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-10 : 08:26:39
|
| In your example, you can be sure that the data in the @AffectedTableIds would correspond to those generated in your insert operation. The reason I can say that with confidence is because of the scope of the @AffectedTableIds table variable.If you were using a temp table, then again, you can be sure - because the scope of the temp table is limited to that session.However, if your output clause was inserting data into a user table or a global temp table, it is very well possible that another process could have inserted more rows into that table between your insert operation and the select operation. |
|
|
|
footmaniac84
Starting Member
12 Posts |
Posted - 2011-05-10 : 10:44:55
|
| Ok, perfect and fast answer. It is my first question in this forum and the result was great.I am very grateful. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-10 : 11:11:27
|
| You are quite welcome and, thank you for the kind words!! |
|
|
|
|
|
|
|
|
Output Clause Issues