Get @@IDENTITY of new inserted record,

I am using VS.net (2003) SQLcommand TEXT. with input params.
SQL server 2000

Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.

My code so far that works... but Where do I put @.@.IDENTITY ?
How do I call or assign the @.@.IDENTITY value to a value in my aspx.vb code page?

Question: how do I get the Identity value from the ID column.
Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help...
-------
INSERT INTO App
(AppName, Acronym, Description,bla bla bla bla......)
VALUES (@.AppName, @.Acronym, @.Description, bla bla bla bla......)

----------

Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click
With cmdAddApp
'.Parameters("@.app_id").Value = Session("App_ID")
.Parameters("@.AppName").Value = txtAppName.Text
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........

End With
Try
cnAppKBdata.Open()
cmdAddApp.ExecuteNonQuery()
cnAppKBdata.Close()
''Session("App_ID") = whatever the @.@.IDENTITY is...'''??
Response.Redirect("AppUpdate.asp")
Catch ex As Exception

End Try
End Sub

Anyone have the lines of code that does this?

Any advise or examples :) thanks you.Well you have a few options here. to name a couple, place it in an output parameter or a return value. I think the better choice is an output param as return is typically reserved for returning custom errors and messages.

.NET Data Access Architecture Guide

Also have a look at scope_identity vs @.@.identity

get @@identity from several records

Hi,

I was wondering if you can help.

I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table.

What I need to do is after I have created these new records update a field in each of them.

Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them?

Ideally I would like to use @.@.identity and datareaders with vb.net.

Cheers

Mark :)

You need to put all the TSQL into a stored procedure. After each insert use SCOPE_IDENTITY() to get the id of the record just inserted.

|||Decorate your table with a new column, let's call it "batchid". It could be a uniqueidentifier.

DECLARE @.batchid uniqueidentifier

SELECT @.batchid=newid()

--The insert

INSERT INTO table (column1,column2 ..... , batchid)
SELECT column1,column2......, @.batchid FROM table WHERE...

--Retrieve the new id:s

SELECT id FROM table WHERE batchid=@.batchid

But it's quite likely that you can insert the correct data directly, instead of having to update it afterwards.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...|||

Although I like the solution of gunteman, I'm just wondering why you can't update the field in the INSERT statement?

|||

Hi,

Thanks for your help. But i would really like to update the field in the insert statement.

Any suggestions?

Cheers

Mark :)

|||

I described it above. If you want to set the new value directly, just include it in your insert statement.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...

If you show us your INSERT and UPDATE statement, we could help you combine them.

Get @@Identity from a remote SQL Server

I am using SQL 2000 server. I have a SP that insert a
record to a remote server table with Identity column as
the ID. After the insert, I try to use @.@.Identity to get
the newly inserted ID back. I got NULL. However, it works
when I run the same SP against a DB on the same server.
How can I get the ID back from a remote server?
Thank you for any inputTom
It works.Thank you a lot.
>--Original Message--
>Try:
>declare @.id int
>exec OtherServer.OtherDB.dbo.sp_executesql
> N'insert MyTable (ColA, ColB) values (@.a, @.b)
> select @.id = @.@.identity'
>, '@.a int, @.b int, @.id int'
>, @.a = 1, @.b = 2, @.id = @.id out
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"omninews" <cxie@.omnichoice.com> wrote in message
news:015801c377f8$140a7410$a001280a@.phx.gbl...
> I am using SQL 2000 server. I have a SP that insert a
>record to a remote server table with Identity column as
>the ID. After the insert, I try to use @.@.Identity to get
>the newly inserted ID back. I got NULL. However, it works
>when I run the same SP against a DB on the same server.
> How can I get the ID back from a remote server?
> Thank you for any input
>|||Tom and others,
It works but sometimes I get dead lock. Because this
exec statement starts a new process on the remote server.
After this I have other update/delete might agaist the
same remote database objects which are within the same
procedure ,therefore the main thread. Looks like the exec
started process does not cleanup itself after it finishes
it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id = @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a = 1, @.b = 2, @.id = @.id out
>>--
>> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message
>news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a
>>record to a remote server table with Identity column as
>>the ID. After the insert, I try to use @.@.Identity to get
>>the newly inserted ID back. I got NULL. However, it
works
>>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Deadlocks are a bigger issue. You'll have to ensure that you keep your =transactions "narrow", i.e. do only the essential work inside the =transaction. Also, make sure you access the resources in the exact same =order in all transactions. You may want to consider doing the insert =via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"omninews" <cxie@.omnichoice.com> wrote in message =news:014f01c3842b$d18f3770$7d02280a@.phx.gbl...
Tom and others,
It works but sometimes I get dead lock. Because this exec statement starts a new process on the remote server. After this I have other update/delete might agaist the same remote database objects which are within the same procedure ,therefore the main thread. Looks like the exec started process does not cleanup itself after it finishes it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id =3D @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a =3D 1, @.b =3D 2, @.id =3D @.id out
>>-- >> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a >>record to a remote server table with Identity column as >>the ID. After the insert, I try to use @.@.Identity to get >>the newly inserted ID back. I got NULL. However, it works >>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Deadlocks are a bigger issue. =You'll have to ensure that you keep your transactions "narrow", i.e. do only the =essential work inside the transaction. Also, make sure you access the resources =in the exact same order in all transactions. You may want to consider =doing the insert via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"omninews" wrote in =message news:014f01c3842b$d1=8f3770$7d02280a@.phx.gbl...Tom and others, It works but sometimes I get dead lock. =Because this exec statement starts a new process on the remote server. After =this I have other update/delete might agaist the same remote database =objects which are within the same procedure ,therefore the main thread. Looks like =the exec started process does not cleanup itself after it finishes =it's job. Is there a way to kill itself after it is done? =Thank you! >--Original Message-->Tom> It works.Thank you a lot.>>>>--Original Message-->Try:>>declare @.id int>exec OtherServer.OtherDB.dbo.sp_executesql> =N'insert MyTable (ColA, ColB) values (@.a, @.b)> =select @.id =3D @.@.identity'>, '@.a int, @.b int, @.id =int'>, @.a =3D 1, @.b =3D 2, @.id =3D @.id =out>>-- > Tom>>---=-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>..>>=;"omninews" =wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...> &=nbsp; I am using SQL 2000 server. I have a SP that insert a >record =to a remote server table with Identity column as >the ID. After =the insert, I try to use @.@.Identity to get >the newly inserted ID =back. I got NULL. However, it works >when I run the same SP =against a DB on the same server.> How can I get the ID back =from a remote server?>> Thank you for any input>>.>

---

--=_NextPart_000_0090_01C3866C.3C5B1D20--

Generic Stored Procedure

I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.

I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.

For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @.tablename, @.code, @.desc, and then create a new record in the table specified by @.tablename.

I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.

Any help would be greatly appreciated!::I would like to create three stored procedures (create, update, delete) that would perform
::the task for any of the lookup tables.

Forget it. Not a feasible way.|||Thanks for your input!

I'm currently writing separate stored procs for each of my lookup tables and will continue to do so until somebody shows me a better way.|||Skip the SP's, go with dynamic SQL and use an intelligent DAL to never maintain the SQL anymore.|||I've written generic stored procedures in the past. The trick is to use the EVAL function of SQL. As an example, here is a stored procedure that would take a table name as parameter and return all records from this table.

The stored procedure is defined as follow:
CREATE PROCEDURE SP_Query
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

You must call it as follow:
SP_Query 'Customers'

As your tables have the same columns it should be easy to built your 3 generic stored procedures in the same way as my example.

Good luck,
Olivier Giulieri
www.kakoo.net|||You can use a mixture of dynamic as static.
Have a utility the writes stored proces and either map them or construct their name.

So...
Template:
Update<table>
Gives:
UpdateMyTable1
UpdateMyTable2
...etc

Then the dynamic part is constructing the name of the stored proc...
CommandText = "Update" + strTableName

PS Don't prefix stored procs with "SP_" it's bad news.|||Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').|||I was just making a quick example of stored procedure to illustrate the use of "EVAL". For sure, I picked a very bad name. Here is my example again:

CREATE PROCEDURE GenericQuery
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

PS: Am I accused of not reading documentations because you said "Forget it. Not a feasible way." and my solution may work? Please let's just try to be constructive here.|||*If* you do need to use an "EVAL" function then consider using sp_executeSQL instead. It's geared up for param' queries.|||<Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').
Easy now big fella! ;)

Check every website that has examples, I will bet 99% use sp_ . For the record I do not, but if you take samples from the net, or even Microsoft you will see they almost always use sp_ so you cannot get too uptight at people for following the convention, even if it does reduce processing efficiency.|||There is no 'good' way to do this.

If you use a new proc for each it's a lot of writing and a lot of changing if it changes. If you use dynamic stored procs you lose the security of refusing anyone direct access to your tables (dynamic stored procs require the use to have table access) and they run much more slowly.

My work around when this happens,

Write one proc per table.
Write one proc which the application calls, it then calls the correct proc. That way your application programmers only need to remember one stored procedure to call and you do the rest.

Avoid using IF statements in an SQL proc.

If you have something like this:

IF X
 Begin
 {code code code}
 End
Else
 Begin
 {code code code}
 End

When you execute only half of the proc will be in the plan and run efficiently. The other half will be adhoc.

Consider this:

If X
 Begin
 exec prCode1
 End
Else
 Begin
 exec prCode2
 End

Since each If calls a procedure that has an execution plan it runs more efficiently (AKA faster).

</code>

Genereal Qusestions

I understan that a mobile databse can not have all the functionaliy like a normal Sql server. Identity and more functions are not included. Is this something that will be i the next versions ?

Have also seen people building classes called SqlCeHelper. Were funtions like @.@. identiy is supported. Is there anyway to build these ? How do they work ?

Thank you for answering my stupid questions

Have a nice evening ! Cheers !

Latso,

I'm not sure i understood your question very well.

@.@.IDENTITY is very much supported in SSEv in select queries. If you can tell me what you are looking for specifically, i could be able to help you better.

Thanks

Pragya

generating an unique number within SSIS - looking for good examples

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Here is some script to generate a new GUID.

Code Snippet

Public Sub Main()

' Generate a globally unique identifier

Dim myGuid As Guid = Guid.NewGuid()

MsgBox(myGuid.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Sergio wrote:

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Like this? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

What do you mean, "generate a new identity value"?

Generating an audit trail in SQLS 2000

Hi,
Following on from a recent post regarding Identity fields with David Portas,
I was discussing a few issues with a collegue and an interesting issue was
raised.
Namely, creating an audit trail in which every row is assigned a unique,
gapless sequential reference. For example, our accountancy package (Sage)
has an audit trail in which every transaction has a numbered reference. Each
number is unique and there are no missing numbers. Obviously some
transactions are cancelled, others may not be assigned in exactly the order
that they were created, etc etc.
I'm just wondering how I would achieve such a thing in SQLS. From talking
with David Portas in my last thread I understand that IDENTITY is not the
way ot achieve this, but I'm curious as to how exactly to achieve this in a
multi-user concurrent environment?
For example, have a unique ID table in which numbers are assigned as they
are required - although I imagine that this would require extensive use of
transactions and locking...
Alternatively would be to have an ON INSERT trigger which simply assigns the
last row reference plus one to the reference field?
Any pointers / sites / etc?
I appreciate that this is a big vague but it's something that I can see
being very useful in the near future.
Any and all advice is gratefully received.
Regards
Chris.One possible solution is at
http://solidqualitylearning.com/blo...04/04/446.aspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Chris Strug" <hotmail@.solace1884.com> wrote in message
news:%23r5dC30PFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Following on from a recent post regarding Identity fields with David
Portas,
> I was discussing a few issues with a collegue and an interesting issue was
> raised.
> Namely, creating an audit trail in which every row is assigned a unique,
> gapless sequential reference. For example, our accountancy package (Sage)
> has an audit trail in which every transaction has a numbered reference.
Each
> number is unique and there are no missing numbers. Obviously some
> transactions are cancelled, others may not be assigned in exactly the
order
> that they were created, etc etc.
> I'm just wondering how I would achieve such a thing in SQLS. From talking
> with David Portas in my last thread I understand that IDENTITY is not the
> way ot achieve this, but I'm curious as to how exactly to achieve this in
a
> multi-user concurrent environment?
> For example, have a unique ID table in which numbers are assigned as they
> are required - although I imagine that this would require extensive use of
> transactions and locking...
> Alternatively would be to have an ON INSERT trigger which simply assigns
the
> last row reference plus one to the reference field?
> Any pointers / sites / etc?
> I appreciate that this is a big vague but it's something that I can see
> being very useful in the near future.
> Any and all advice is gratefully received.
> Regards
> Chris.
>
>|||If you don't mind having gaps in numbers i.e reserve IDs before you
write. Sticking the next available ID in a one row table is good
performance wise. You only lock one very small table rather than the
table you are inserting data into.
The processes that will write can take their time because they have
their unique IDs already and so don't stop other writes reserving
their IDs. Also you can reserve 100 IDs very easily at no cost just by
adding 100.
But you will get gaps, after reserving ID if your write fails.

generate Identity field on existing table

Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?

Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.

Thanks in advance!do you mean programmatically ? you can always add it in the design view...

hth|||If you add a new column and set its type to INT and set Identity to true, it will auto populate based on the Identity Seed and the Identity Increment.|||Thanks for the replies. I figured it out right after my first posting. DOH! :)