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 |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2008-11-11 : 11:25:17
|
| I expect a straight "no" on this one, but is there a way to select from the inserted table, without making a stored procedure? The reason I ask is I'm inserting a record into a table, via SQL pass-through (or ad-hoc SQL) ( i.e. Insert TableName ({TableFields}) Values ({TableValues}) ). And I'd like to retrieve the ID that was auto-generated due to the insert statement. But I don't wish to create a stored proc for this task. I'd appreciate any help.Thank you. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-11 : 11:52:55
|
| Maybe I'm not following you, but why can't you just use SCOPE_IDENTITY() to get ID after the insert? |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-11-12 : 07:01:27
|
| I'd be more concerned as to your objection to using a StoredProcedure.SP's are considered part of "best practice" from performance, security and code maintainability standpoints. |
|
|
|
malaytech2008
Yak Posting Veteran
95 Posts |
Posted - 2008-11-12 : 08:55:20
|
| I don't think you will get an ID from table Inserted without writing trigger.This table is used in temp database before writing anything in Datafile during transaction.malay |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 09:38:13
|
quote:
Originally posted by malaytech2008
I don't think you will get an ID from table Inserted without writing trigger.This table is used in temp database before writing anything in Datafile during transaction.malay
why do you think you cant use SCOPE_IDENTITY to get last inserted id as suggested by Lamprey? |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-11-12 : 10:50:08
|
| I didn't use SCOPE_IDENTITY() because I was not aware such a command existed. In hindsight, I probably should've realized the @@IDENTITY command has a cousin. And I have no problem with using SPs; I was curious if there were other means to retrieve data from the inserted table. It worked, though, so thank you!!! This helps quite a bit! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 11:50:35
|
quote:
Originally posted by SQLIsTheDevil
I didn't use SCOPE_IDENTITY() because I was not aware such a command existed. In hindsight, I probably should've realized the @@IDENTITY command has a cousin. And I have no problem with using SPs; I was curious if there were other means to retrieve data from the inserted table. It worked, though, so thank you!!! This helps quite a bit!
see this to understand different ways to get identity valueshttp://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ |
|
|
|
|
|
|
|
|
is there a way to select inserted table w/o procs?