Updating an identity column
SQL Server 2005 introducted the OUTPUT clause which we can use to capture values from the inserted and deleted virtual tables.Previously this data was only available through triggers. SELECT statement to capture all the inserted identity values.If you try to use @@IDENTITY or SCOPE_IDENTITY, you will find it only returns the Identity Column ID when the insert is triggered.\r\n\r\n Introducing MS SQL's OUTPUT clause, introduced in MS SQL 2005.We can use the OUTPUT clause to return the Identity Column ID(s).\r\n\r\n When we add this to the INSERT and UPDATE clauses, the problem we run into is that during an insert it will return an empty set and another set containing the Identity Column ID that was inserted.Therefore we use a temporary table to help us with this.
Plus the temporary table will not need disk access.
There are two ways to synchronize identity values generated by the data source: use either the first returned record or the output parameters of a stored procedure. Event Args e) column will have new identity values generated by the data source when they are updated back to the data source.
The sample uses a single stored procedure: CREATE PROCEDURE Insert Categories @Category Id int output, @Category Name nvarchar(15), @Description ntext AS SET NOCOUNT ON insert Categories( Category Name, Description) values ( @Category Name, @Description) if @@rowcount=0 return 1 set @Category ID = Scope_Identity( ) select Scope_Identity( ) Category Id return 0// Namespaces, variables, and constants using System; using System. There are two ways in which the data source generated value can be retrieved and this solution demonstrates both.
The // Create the insert command for the Data Adapter.
String sql Text="INSERT Categories(Category Name, Description) VALUES" "(@Category Name, @Description);" "SELECT Scope_Identity( ) Category Id"; da.