Last post Apr 15, 2012 02:17 PM by human2x
Apr 10, 2012 05:21 PM|human2x|LINK
I have two tables (master / detail)
The master table has a (Invoice No) primary key value inserted automatically as (Auto Increment) number.
The details table has (Invoice No) as a foreign key from master table.
Once I insert a record to master table the (Invoice No) is generated automatically.
How can I get this generated value so I can use it to insert the related records to the
Apr 10, 2012 05:24 PM|kidshaw|LINK
I would put a stored procedure in to handle the insert, then use SCOPE_IDENTITY to return the key as either a return value or as I prefer, an output parameter.
Apr 10, 2012 05:30 PM|human2x|LINK
Actually I didn't use stored procedure for that.
I insert directly into tables.
Is there any way to get the inserted value.
Apr 10, 2012 06:05 PM|kidshaw|LINK
Not that I know of - you need a return value.
Apr 10, 2012 06:11 PM|human2x|LINK
while searching I got this link:
I will try it and see.
Apr 11, 2012 04:13 AM|kidshaw|LINK
Just be wary of what happens when concurrent inserts happen. SCOPE_IDENTITY in SQL Server returns the IDENTITY result of the last insert in the current scope. If two inserts occur before the first IDENTITY is returned, it doesn't matter as they are scoped.
Make sure this method is scope aware, otherwise it will be subject to this pitfall and you could theretically end up with child records linked to the wrong master record.
For small scale applications, it possibly won't be an issue - or at least it won't occur for some time. But if your system is going to scale up - this could be a nightmare to resolve later on.
Hope this helps
Apr 15, 2012 02:17 PM|human2x|LINK
I tried :
It worked fine.