SCOPE_IDNTITY()

Hi All,
How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
appreciated.
Regards
Muralicreate table dbo.foo(id INT IDENTITY(1,1), name varchar(30))
go
insert dbo.foo(name) select 'bar'
select scope_identity()
go
drop table dbo.foo
go
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||A scope is a module -- a stored procedure, trigger, function, or batch.
Eg: from Books online.
This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a
row in TY.
USE tempdb
GO
CREATE TABLE TZ (
Z_id int IDENTITY(1,1)PRIMARY KEY,
Z_name varchar(20) NOT NULL)
INSERT TZ
VALUES ('Lisa')
INSERT TZ
VALUES ('Mike')
INSERT TZ
VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ looks
Z_id Z_name
--
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY (
Y_id int IDENTITY(100,5)PRIMARY KEY,
Y_name varchar(20) NULL)
INSERT TY (Y_name)
VALUES ('boathouse')
INSERT TY (Y_name)
VALUES ('rocks')
INSERT TY (Y_name)
VALUES ('elevator')
SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
--
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
BEGIN
INSERT TY VALUES ('')
END
/*FIRE the trigger and find out what identity values you get
with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @.@.IDENTITY AS [@.@.IDENTITY]
GO
--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which
was the insert on table TZ*/
@.@.IDENTITY
115
/*@.@.IDENTITY returned the last identity value inserted to TY by the trigger,
which fired due to an earlier insert on TZ*/
Thanks
Hari
SQL Server MVP
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||Read up about it in the BOL.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||Example:
create table t1 (colA int not null identity unique)
go
create table t2 (colA int not null identity(100, 1) unique)
go
create trigger tr_t1_ins on t1
for insert
as
insert into t2 default values
go
insert into t1 default values
select scope_identity(), @.@.identity
go
drop table t1, t2
go
AMB
"Murali" wrote:

> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>
>|||Thank you very much Hari.
Regards
Murali
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uSMlykaRFHA.3972@.TK2MSFTNGP14.phx.gbl...
> A scope is a module -- a stored procedure, trigger, function, or batch.
> Eg: from Books online.
> This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
> When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts
a
> row in TY.
> USE tempdb
> GO
> CREATE TABLE TZ (
> Z_id int IDENTITY(1,1)PRIMARY KEY,
> Z_name varchar(20) NOT NULL)
> INSERT TZ
> VALUES ('Lisa')
> INSERT TZ
> VALUES ('Mike')
> INSERT TZ
> VALUES ('Carla')
> SELECT * FROM TZ
> --Result set: This is how table TZ looks
> Z_id Z_name
> --
> 1 Lisa
> 2 Mike
> 3 Carla
> CREATE TABLE TY (
> Y_id int IDENTITY(100,5)PRIMARY KEY,
> Y_name varchar(20) NULL)
> INSERT TY (Y_name)
> VALUES ('boathouse')
> INSERT TY (Y_name)
> VALUES ('rocks')
> INSERT TY (Y_name)
> VALUES ('elevator')
> SELECT * FROM TY
> --Result set: This is how TY looks:
> Y_id Y_name
> --
> 100 boathouse
> 105 rocks
> 110 elevator
> /*Create the trigger that inserts a row in table TY
> when a row is inserted in table TZ*/
> CREATE TRIGGER Ztrig
> ON TZ
> FOR INSERT AS
> BEGIN
> INSERT TY VALUES ('')
> END
> /*FIRE the trigger and find out what identity values you get
> with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
> INSERT TZ VALUES ('Rosalie')
> SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
> GO
> SELECT @.@.IDENTITY AS [@.@.IDENTITY]
> GO
> --Here is the result set.
> SCOPE_IDENTITY
> 4
> /*SCOPE_IDENTITY returned the last identity value in the same scope, which
> was the insert on table TZ*/
> @.@.IDENTITY
> 115
> /*@.@.IDENTITY returned the last identity value inserted to TY by the
trigger,
> which fired due to an earlier insert on TZ*/
> Thanks
> Hari
> SQL Server MVP
> "Murali" <ivantagemurali@.gmail.com> wrote in message
> news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
highly[vbcol=seagreen]
>

SCOPE_IDNTITY()

Hi All,
How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
appreciated.
Regards
Muralicreate table dbo.foo(id INT IDENTITY(1,1), name varchar(30))
go
insert dbo.foo(name) select 'bar'
select scope_identity()
go
drop table dbo.foo
go
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||A scope is a module -- a stored procedure, trigger, function, or batch.
Eg: from Books online.
This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a
row in TY.
USE tempdb
GO
CREATE TABLE TZ (
Z_id int IDENTITY(1,1)PRIMARY KEY,
Z_name varchar(20) NOT NULL)
INSERT TZ
VALUES ('Lisa')
INSERT TZ
VALUES ('Mike')
INSERT TZ
VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ looks
Z_id Z_name
--
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY (
Y_id int IDENTITY(100,5)PRIMARY KEY,
Y_name varchar(20) NULL)
INSERT TY (Y_name)
VALUES ('boathouse')
INSERT TY (Y_name)
VALUES ('rocks')
INSERT TY (Y_name)
VALUES ('elevator')
SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
--
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
BEGIN
INSERT TY VALUES ('')
END
/*FIRE the trigger and find out what identity values you get
with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @.@.IDENTITY AS [@.@.IDENTITY]
GO
--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which
was the insert on table TZ*/
@.@.IDENTITY
115
/*@.@.IDENTITY returned the last identity value inserted to TY by the trigger,
which fired due to an earlier insert on TZ*/
Thanks
Hari
SQL Server MVP
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||Read up about it in the BOL.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||Example:
create table t1 (colA int not null identity unique)
go
create table t2 (colA int not null identity(100, 1) unique)
go
create trigger tr_t1_ins on t1
for insert
as
insert into t2 default values
go
insert into t1 default values
select scope_identity(), @.@.identity
go
drop table t1, t2
go
AMB
"Murali" wrote:
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>
>|||Thank you very much Hari.
Regards
Murali
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uSMlykaRFHA.3972@.TK2MSFTNGP14.phx.gbl...
> A scope is a module -- a stored procedure, trigger, function, or batch.
> Eg: from Books online.
> This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
> When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts
a
> row in TY.
> USE tempdb
> GO
> CREATE TABLE TZ (
> Z_id int IDENTITY(1,1)PRIMARY KEY,
> Z_name varchar(20) NOT NULL)
> INSERT TZ
> VALUES ('Lisa')
> INSERT TZ
> VALUES ('Mike')
> INSERT TZ
> VALUES ('Carla')
> SELECT * FROM TZ
> --Result set: This is how table TZ looks
> Z_id Z_name
> --
> 1 Lisa
> 2 Mike
> 3 Carla
> CREATE TABLE TY (
> Y_id int IDENTITY(100,5)PRIMARY KEY,
> Y_name varchar(20) NULL)
> INSERT TY (Y_name)
> VALUES ('boathouse')
> INSERT TY (Y_name)
> VALUES ('rocks')
> INSERT TY (Y_name)
> VALUES ('elevator')
> SELECT * FROM TY
> --Result set: This is how TY looks:
> Y_id Y_name
> --
> 100 boathouse
> 105 rocks
> 110 elevator
> /*Create the trigger that inserts a row in table TY
> when a row is inserted in table TZ*/
> CREATE TRIGGER Ztrig
> ON TZ
> FOR INSERT AS
> BEGIN
> INSERT TY VALUES ('')
> END
> /*FIRE the trigger and find out what identity values you get
> with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
> INSERT TZ VALUES ('Rosalie')
> SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
> GO
> SELECT @.@.IDENTITY AS [@.@.IDENTITY]
> GO
> --Here is the result set.
> SCOPE_IDENTITY
> 4
> /*SCOPE_IDENTITY returned the last identity value in the same scope, which
> was the insert on table TZ*/
> @.@.IDENTITY
> 115
> /*@.@.IDENTITY returned the last identity value inserted to TY by the
trigger,
> which fired due to an earlier insert on TZ*/
> Thanks
> Hari
> SQL Server MVP
> "Murali" <ivantagemurali@.gmail.com> wrote in message
> news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> >
> > How to use the SCOPE_IDENTITY() in SQL Server, any example will be
highly
> > appreciated.
> >
> > Regards
> > Murali
> >
> >
>

SCOPE_IDNTITY()

Hi All,
How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
appreciated.
Regards
Murali
create table dbo.foo(id INT IDENTITY(1,1), name varchar(30))
go
insert dbo.foo(name) select 'bar'
select scope_identity()
go
drop table dbo.foo
go
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>
|||A scope is a module -- a stored procedure, trigger, function, or batch.
Eg: from Books online.
This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a
row in TY.
USE tempdb
GO
CREATE TABLE TZ (
Z_id int IDENTITY(1,1)PRIMARY KEY,
Z_name varchar(20) NOT NULL)
INSERT TZ
VALUES ('Lisa')
INSERT TZ
VALUES ('Mike')
INSERT TZ
VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ looks
Z_id Z_name
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY (
Y_id int IDENTITY(100,5)PRIMARY KEY,
Y_name varchar(20) NULL)
INSERT TY (Y_name)
VALUES ('boathouse')
INSERT TY (Y_name)
VALUES ('rocks')
INSERT TY (Y_name)
VALUES ('elevator')
SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
BEGIN
INSERT TY VALUES ('')
END
/*FIRE the trigger and find out what identity values you get
with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @.@.IDENTITY AS [@.@.IDENTITY]
GO
--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which
was the insert on table TZ*/
@.@.IDENTITY
115
/*@.@.IDENTITY returned the last identity value inserted to TY by the trigger,
which fired due to an earlier insert on TZ*/
Thanks
Hari
SQL Server MVP
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>
|||Read up about it in the BOL.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>
|||Example:
create table t1 (colA int not null identity unique)
go
create table t2 (colA int not null identity(100, 1) unique)
go
create trigger tr_t1_ins on t1
for insert
as
insert into t2 default values
go
insert into t1 default values
select scope_identity(), @.@.identity
go
drop table t1, t2
go
AMB
"Murali" wrote:

> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>
>
|||Thank you very much Hari.
Regards
Murali
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uSMlykaRFHA.3972@.TK2MSFTNGP14.phx.gbl...
> A scope is a module -- a stored procedure, trigger, function, or batch.
> Eg: from Books online.
> This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
> When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts
a
> row in TY.
> USE tempdb
> GO
> CREATE TABLE TZ (
> Z_id int IDENTITY(1,1)PRIMARY KEY,
> Z_name varchar(20) NOT NULL)
> INSERT TZ
> VALUES ('Lisa')
> INSERT TZ
> VALUES ('Mike')
> INSERT TZ
> VALUES ('Carla')
> SELECT * FROM TZ
> --Result set: This is how table TZ looks
> Z_id Z_name
> --
> 1 Lisa
> 2 Mike
> 3 Carla
> CREATE TABLE TY (
> Y_id int IDENTITY(100,5)PRIMARY KEY,
> Y_name varchar(20) NULL)
> INSERT TY (Y_name)
> VALUES ('boathouse')
> INSERT TY (Y_name)
> VALUES ('rocks')
> INSERT TY (Y_name)
> VALUES ('elevator')
> SELECT * FROM TY
> --Result set: This is how TY looks:
> Y_id Y_name
> --
> 100 boathouse
> 105 rocks
> 110 elevator
> /*Create the trigger that inserts a row in table TY
> when a row is inserted in table TZ*/
> CREATE TRIGGER Ztrig
> ON TZ
> FOR INSERT AS
> BEGIN
> INSERT TY VALUES ('')
> END
> /*FIRE the trigger and find out what identity values you get
> with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
> INSERT TZ VALUES ('Rosalie')
> SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
> GO
> SELECT @.@.IDENTITY AS [@.@.IDENTITY]
> GO
> --Here is the result set.
> SCOPE_IDENTITY
> 4
> /*SCOPE_IDENTITY returned the last identity value in the same scope, which
> was the insert on table TZ*/
> @.@.IDENTITY
> 115
> /*@.@.IDENTITY returned the last identity value inserted to TY by the
trigger,[vbcol=seagreen]
> which fired due to an earlier insert on TZ*/
> Thanks
> Hari
> SQL Server MVP
> "Murali" <ivantagemurali@.gmail.com> wrote in message
> news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
highly
>

SCOPE_IDENTITY()??

Hi All,
I have one doubt, if any one can help-me:
I have one SQL(db) that receive a lot of inserts, and I need get IDENTITY
value of current insert, but the machine have 4 processors.
If I use SCOPE_IDENTITY() I will get the corret value?
Thanks.Yes.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OoNsEb$0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have one doubt, if any one can help-me:
> I have one SQL(db) that receive a lot of inserts, and I need get IDENTITY
> value of current insert, but the machine have 4 processors.
> If I use SCOPE_IDENTITY() I will get the corret value?
> Thanks.
>|||Might want to try IDENT_CURRENT as SCOPE_IDENTITY is scope specific.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OoNsEb$0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have one doubt, if any one can help-me:
> I have one SQL(db) that receive a lot of inserts, and I need get IDENTITY
> value of current insert, but the machine have 4 processors.
> If I use SCOPE_IDENTITY() I will get the corret value?
> Thanks.
>|||> Might want to try IDENT_CURRENT as SCOPE_IDENTITY is scope specific.
Actually, that is a lot more dangerous. Unless I read it wrong, the OP
wants the IDENTITY value generated by the current insert (SCOPE_IDENTITY),
not the most current IDENTITY value for the table (which may have changed
since the most recent insert in the current scope).
A

SCOPE_IDENTITY() vs. @@IDENTITY

I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.

If I do the INSERT followed by a "SELECT @.@.IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @.@.IDENTITY.

I guess what I have is satisfactory but this was frustrating and I want to know why.

This is on SQL Server 2000 Standard edition with version SP3a + hot fixeshai roger,

SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope. @.@.IDENTITY is not limited to a specific scope.

This is the example given in BOL

Suppose if you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Hope this relives u of ur frustration|||Yes, I read BOL and understand the documented theoretical and conceptual differences between the two. However, those differences don't apply to my situation.

There is only one thread, one process, one identity value, and one table involved. There are no triggers or functions or stored procs involved. It's a simple INSERT/get ID situation. And @.@.IDENTITY works and SCOPE_IDENTITY doesn't work which just doesn't make any sense according to what I've read.

SCOPE_IDENTITY() SqlCe problem

I unable to do the SCOPE_IDENTITY on a insert query at SQL Mobile.

I need to perform on this query the insert on Client table and to get the ID.

I am programming on Visual Basic on Visual Studio 2005

My code is:

Dim ClientID as Integer

Dim sql As String = "INSERT INTO Client(Number) VALUES('5'); SELECT scope_identity()"

Dim cmd As New SqlCeCommand(sql, connection)

connection.Open()

ClientID = Convert.ToInt32(cmd.ExecuteNonQuery())

connection.Close()

And I get an error like this:

There was an error parsing the query. [ Token line number = 1,Token line offset = 76,Token in error = SELECT ]

Thanks!

You cannot have more queries in the same batch and scope_identity is not supported by SQL CE/Mobile. Use "SELECT @.@.IDENTITY" in a second .ExecuteNonQuery call.

SCOPE_IDENTITY() returning the ID value of an inserted record.

There are loads of postings on the net about this problem but none I have found explain the cause.

Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.

Has anyone come across the solution for this issue?

Thanks

Can you provide your code?|||

Here is the Stored Proc

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[InsertNewEnquiry_Client]

(

@.Salutation

varchar(10),

@.Name

varchar(60),

@.Address1

varchar(60),

@.Address2

varchar(60),

@.Address3

varchar(60),

@.Town

nvarchar(50),

@.PostCode

char(10),

@.County

char(60),

@.Telephone1

char(12),

@.Telephone2

char(12),

@.email

char(30)

)

AS

SETNOCOUNTOFF;

INSERT

INTO tblClient(Salutation,Name, Address1, Address2, Address3, Town, PostCode, County, Telephone1, Telephone2, email)

VALUES

(@.Salutation,@.Name,@.Address1,@.Address2,@.Address3,@.Town,@.PostCode,@.County,@.Telephone1,@.Telephone2,@.email);

SELECT

ClientID, Salutation,Name, Address1, Address2, Address3, PostCode, County, Telephone1, Telephone2, Telephone3, emailFROM tblClientWHERE(ClientID=SCOPE_IDENTITY())|||And can you show us the code which calls this stored procedure?|||int ClientID = Convert.ToInt32(clientTableAdapter.InsertStoredProc(cboClientSalutation.SelectedItem, txtClientName,text ......) );|||

The 1 your getting is most likely the default result of an Insert which is to return the number of records that have been entered.

I think you need to set the ExecuteMode property on your function to scalar.

|||

Actually, I've just looked at your sql again and your selecting an entire record at the end, not the identity on its own.

Simply put SELECT SCOPE_IDENTITY() if you want just the id returned, plus make sure you set the executemode on the function to scalar as I said before.

Hope that helps.

SCOPE_IDENTITY() problems

No matter how hard I look, I can't find a suitable answer to the many questions people tend to have about retriving an identity value after an SQL Insert command, particularly in C#. Everyone keeps going on about the advantages of Scope_Identity over @.@.IDENTITY but nobody seems to have actually explained properly how to use it ... hence my problems and frustrations.

I can use @.@.Identity fine since it returns a simple value (an int, I think). But the site I'm developing will apparently have heavy traffic (a similar site by the client uses 25Gb of bandwidth per month) so @.@.Identity is probably out of the question.

The problem I have is that Scope_Identity, along with the SqlCommand.ExecuteScalar() method returns an object, which is not what I want. I can't cast the object into an int.

I'm presently just using SqlConnection and SqlCommand classes to build and execute SQL queries (no data adapters in sight) so I need to know how to use the SCOPE_IDENTITY within that context.

Another question: I'm using the command builder to create the SQL commands. I've noticed some examples adding a SELECT @.thisId = SCOPE_IDENTITY(). However, the command builder doesn't like this syntax (?)Quick question to you, how would return any integer value? It's really no different. There is absolutley no difference (in terms of consuming it) between @.@.Identity and SCOPE_IDENTITY. Ones a variable that you can select back, and ones a function that you can select back.|||Well, here's the code I've been using:

On one part of the application, I'm using:

SqlCommand idCMD = new SqlCommand("SELECT @.@.IDENTITY",conTempProperties);
int prop_id = Int32.Parse(idCMD.ExecuteScalar().ToString());

and on another, I'm using:

SqlCommand sqlCMD = new SqlCommand("SELECT SCOPE_IDENTITY()",conClient);
client_id = Int32.Parse(sqlCMD.ExecuteScalar().ToString());

The first one works, while the second one gives me an 'Input string was not in a correct format' error. So scope_identity and @.@.IDENTITY obviously don't return the same data types (?).|||check out BOL for the xact differences. heres the cut/pasted info from BOL :

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Syntax
SCOPE_IDENTITY( )
Return Types
sql_variant
Remarks
SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.
SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

hth|||I'd advise that you really want to put that code with insert/update. It's supposed to be in the same context as the batch/command you've just run. Simply selecting scope_identity without doing any work will return NULL.|||Thanks for that.

I'll try changing the way I've done the scope_identity(). I did come across someone using it with ExecuteNonQuery rather than ExecuteScalar and tried it. It seemed to work on that occasion but it returned a null - no doubt because I hadn't included it in the same batch as the INSERT command.|||I've been trying to add the scope_identity to the end of the INSERT statement with the command builder but I keep getting a parse error. How do I actually do it? What's the correct syntax?|||Take a look at the SQL the command builder...builds. Plus what syntax are you trying to use?|||Well, I'm trying something like:

INSERT INTO clients (business_name,address,town)
VALUES (@.business_name,@.address,@.town);
SELECT SCOPE_IDENTITY() AS ident

I have to admit I'm flying blind here. I've also tried the following:

INSERT INTO clients (business_name,address,town)
VALUES (@.business_name,@.address,@.town);
SELECT @.ident = SCOPE_IDENTITY()

Errr ...|||

INSERT INTO clients (business_name,address,town)
VALUES (@.business_name,@.address,@.town) SELECT @.ident = SCOPE_IDENTITY()

should work pretty good.

hth|||*Sigh* Still getting a parsing error. Maybe the command builder doesn't allow you to create complex commands?

Anyway, I'm going to try doing things programmatically to see if that works.|||are you using a stored proc ? can you post the relevant code ?|||You *need* to read this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp

SCOPE_IDENTITY() Help

I was wondering if its possible to use this function if your using aSqlDataReader where I just run a stored procedure that just inserts arow?

Better might be a return value that you get at via a Return Code parameter using ExecuteNonQuery. No reason you could not do a SELECT SCOPE_IDENTITY() at the end of an INSERT SP and then get at it using ExecuteScalar() if you wish.

CREATE PROCEDURE spfoo
@.fooID int
AS
INSERT INTO fooTable(fooID) VALUES(@.fooID)
RETURN SCOPE_IDENTITY()

|||

Hi
Using SQLDataSource and SQL 2005 Stored Procedure I've been successful in creating multiple additional CreateUser fields saving them to "tbl_UserDetails" table. 
I pass the UserId to the tbl_UserDetails as well.  What I'm struggling with is retreiving the new tbl_UserDetails "Details_Id" - t-sql RETURN SCOPE_IDENTITY()).
I want to integrate it into the Profile data (see Profile creation below in CompleteButton_Click (71)). 
I'm not sure if the problem retreiving the Details_Id is in the SQLDSInsert_Inserted (133) code used or if I'm not addressing the retreival in the "correct" stage of "the process".  See below.
Thanks

1Imports Telerik.WebControls2Imports SITE.DAL.DBAccess3Imports System.Data4Imports System.Data.SqlClient56PartialClass Site_Pages_Registration7Inherits System.Web.UI.Page8Protected WithEvents Update_DateAs WebControls.HiddenField910Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load11If Session("Record") IsNotNothing Then12 CreateUserWizard1.MoveTo(CompleteWizardStep1)13End If1415 End Sub1617 Protected Sub CreateUserWizard1_CreatedUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles CreateUserWizard1.CreatedUser18Dim userAs MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)1920If userIs Nothing Then21 Throw New ApplicationException("Can't find the user.")22 End If2324 Dim DetailsInsert As SqlDataSource = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("SQLDSInsert"), SqlDataSource)25 Dim UserId As Guid = DirectCast(user.ProviderUserKey, Guid)26 Session("NewUserId") = UserId27 Dim Details_FirstName As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox)28 Dim Details_LastName As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox)29 Dim Details_MI As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_MI"), TextBox)30 Dim Details_Title As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox)31 Dim Details_Company As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox)32 Dim Details_1Comm As Telerik.WebControls.RadMaskedTextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox)33 Dim Details_1CommType As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox)34 Dim Details_2Comm As Telerik.WebControls.RadMaskedTextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox)35 Dim Details_2CommType As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox)36 Dim Details_Address As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox)37 Dim Details_City As TextBox = CType(CreateUserWizardStep0.ContentTemplateContainer.FindControl("Details_City"), TextBox)38 Dim Details_State As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox)39 Dim Details_Zip As Telerik.WebControls.RadMaskedTextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox)40 Dim Details_UserTypeId As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_UserTypeId"), Telerik.WebControls.RadComboBox)41 Dim Details_ReceiveEmail As CheckBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_ReceiveEmail"), CheckBox)4243 DetailsInsert.Insert()4445 Dim UserType As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_UserTypeId"), Telerik.WebControls.RadComboBox)46 Session("UserType") = UserType.SelectedValue.ToString47 End Sub4849 Protected Sub SQLDSInsert_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)50 Dim user As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)5152 e.Command.Parameters("@.UserId").Value = user.ProviderUserKey53 e.Command.Parameters("@.Details_FirstName").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox).Text54 e.Command.Parameters("@.Details_LastName").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox).Text55 e.Command.Parameters("@.Details_MI").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_MI"), TextBox).Text56 e.Command.Parameters("@.Details_Company").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox).Text57 e.Command.Parameters("@.Details_Title").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox).Text58 e.Command.Parameters("@.Details_1Comm").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox).Text59 e.Command.Parameters("@.Details_1CommType").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox).SelectedValue60 e.Command.Parameters("@.Details_2Comm").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox).Text61 e.Command.Parameters("@.Details_2CommType").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox).SelectedValue62 e.Command.Parameters("@.Details_Address").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox).Text63 e.Command.Parameters("@.Details_City").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_City"), TextBox).Text64 e.Command.Parameters("@.Details_State").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox).SelectedValue65 e.Command.Parameters("@.Details_Zip").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox).Text66 e.Command.Parameters("@.Details_UserTypeId").Value = Int32.Parse(CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_UserTypeId"), Telerik.WebControls.RadComboBox).SelectedValue)67 e.Command.Parameters("@.Details_ReceiveEmail").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_ReceiveEmail"), CheckBox).Checked68 End Sub697071 Protected Sub CompleteButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)72 Dim user As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)7374 Dim pb As ProfileBase = ProfileBase.Create(user.UserName)75 pb.SetPropertyValue("FirstName", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox).Text)76 pb.SetPropertyValue("LastName", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox).Text)77 pb.SetPropertyValue("UserName", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text)78 pb.SetPropertyValue("Record", Session("Record"))79 pb.SetPropertyValue("Company", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox).Text)80 pb.SetPropertyValue("Title", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox).Text)81 pb.SetPropertyValue("Comm1", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox).Text)82 pb.SetPropertyValue("Comm1Type", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox).SelectedValue)83 pb.SetPropertyValue("Comm2", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox).Text)84 pb.SetPropertyValue("Comm2Type", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox).SelectedValue)85 pb.SetPropertyValue("Address", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox).Text)86 pb.SetPropertyValue("City", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_City"), TextBox).Text)87 pb.SetPropertyValue("State", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox).SelectedValue)88 pb.SetPropertyValue("Zip", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox).Text)8990 pb.Save()9192 Dim lblRegisterCompleteName As Label = CreateUserWizard1.CompleteStep.FindControl("lblRegisterCompleteName"), Label93 lblRegisterCompleteName.Text = Profile.FirstName & " " & Profile.LastName94 Dim Muser As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)95 If Muser Is Nothing Then96 Throw New ApplicationException("Can't find the user.")97End If98 Muser.IsApproved =False99 End Sub100101 Protected Sub btnTeamProfile_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)102Dim userAs MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)103104Dim pbAs ProfileBase = ProfileBase.Create(user.UserName)105 pb.SetPropertyValue("FirstName",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox).Text)106 pb.SetPropertyValue("LastName",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox).Text)107 pb.SetPropertyValue("UserName",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text)108 pb.SetPropertyValue("Record", Session("Record"))109 pb.SetPropertyValue("Company",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox).Text)110 pb.SetPropertyValue("Title",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox).Text)111 pb.SetPropertyValue("Comm1",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox).Text)112 pb.SetPropertyValue("Comm1Type",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox).SelectedValue)113 pb.SetPropertyValue("Comm2",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox).Text)114 pb.SetPropertyValue("Comm2Type",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox).SelectedValue)115 pb.SetPropertyValue("Address",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox).Text)116 pb.SetPropertyValue("City",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_City"), TextBox).Text)117 pb.SetPropertyValue("State",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox).SelectedValue)118 pb.SetPropertyValue("Zip",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox).Text)119120 pb.Save()121 Response.Redirect("Profile.aspx")122End Sub123124 Protected Sub CompleteWizardStep1_PreRender(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles CompleteWizardStep1.PreRender125Dim btnTeamProfileAs Button =CType(CompleteWizardStep1.ContentTemplateContainer.FindControl("btnTeamProfile"), Button)126If Session("UserType") ="2"Then127 btnTeamProfile.Visible =True128 Else129 btnTeamProfile.Visible =False130 End If131 End Sub132133 Protected Sub SQLDSInsert_Inserted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)134Dim DetailsInsertAs SqlDataSource =CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("SQLDSInsert"), SqlDataSource)135Dim RecordAs Integer = DetailsInsert.InsertParameters.Add("@.Details_Id", System.Data.ParameterDirection.ReturnValue)136'Dim Record As Integer = e.Command.ExecuteScalar137 Session("Record") = Record.ToString138139 Response.Redirect("Registration.aspx")140End Sub141142143End Class144

SCOPE_IDENTITY() and "instead of" Triggers.

Here's a fun question :)
If I have an instead of trigger on a table, which replaces the insert, how
can I get the identity insert from the data inserted?
-- example --
IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
CLUSTERED , Data1 CHAR(1) NOT NULL )
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
GO
CREATE TRIGGER
TR_dbo_tblTest
ON
dbo.tblTest
INSTEAD OF INSERT
AS
SET NOCOUNT ON
IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
BEGIN
INSERT INTO
dbo.tblTest ( Data1 )
SELECT
Data1
FROM
inserted
PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
END
GO
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
-- The following should return 2, but it returns NULL because the insert
was done by the trigger.
PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
'<NULL>' ) -- NULLI could be wrong, but are trying to get the ID for the record inserted?
If you are... When I use Int Identity fields for the primary key, I use a SP
to insert my records when I need to know what the primary key is.
For example, this SP is used to insert a new record into the Contacts table.
It returns a result set with a field called NewID that contains the ID for
the new record.
Create Procedure [dbo].[NewContactRec_SP]
@.LName VARCHAR(30),
@.FName VARCHAR(20),
@.ResID Int,
@.StaffID Int
as
Insert Into Contacts
(LName,FName,ResID,CreatedStaffID)
Values (@.LName,@.Fname,@.ResID,@.StaffID)
/* Return New ID */
Select SCOPE_IDENTITY() As NewID
HTH,
-Steve-|||For INSTEAD OF triggers, use the ol' @.@.IDENTITY instead:
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
PRINT 'T-SQL: B ->' + ISNULL( CAST( @.@.IDENTITY AS VARCHAR(11)) ,'<NULL>' )
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
> Here's a fun question :)
> If I have an instead of trigger on a table, which replaces the insert, how
> can I get the identity insert from the data inserted?
>
> -- example --
> IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
> CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
> CLUSTERED , Data1 CHAR(1) NOT NULL )
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
> PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
>
> GO
> CREATE TRIGGER
> TR_dbo_tblTest
> ON
> dbo.tblTest
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
> BEGIN
> INSERT INTO
> dbo.tblTest ( Data1 )
> SELECT
> Data1
> FROM
> inserted
> PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
> END
> GO
>
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> -- The following should return 2, but it returns NULL because the insert
> was done by the trigger.
> PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
> '<NULL>' ) -- NULL
>|||We have a ton of auditing code inside the trigger and can't change it to a
stored proc, mainly because people can still edit the data in SQL-EM and
these changes still need to be audited.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:eEstw$$0FHA.3660@.TK2MSFTNGP15.phx.gbl...
> I could be wrong, but are trying to get the ID for the record inserted?
> If you are... When I use Int Identity fields for the primary key, I use a
SP
> to insert my records when I need to know what the primary key is.
> For example, this SP is used to insert a new record into the Contacts
table.
> It returns a result set with a field called NewID that contains the ID for
> the new record.
> Create Procedure [dbo].[NewContactRec_SP]
> @.LName VARCHAR(30),
> @.FName VARCHAR(20),
> @.ResID Int,
> @.StaffID Int
> as
> Insert Into Contacts
> (LName,FName,ResID,CreatedStaffID)
> Values (@.LName,@.Fname,@.ResID,@.StaffID)
> /* Return New ID */
> Select SCOPE_IDENTITY() As NewID
> HTH,
> -Steve-
>|||Ok,
But that requires an exclusive table lock to stop people putting in more
data :)
I tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,
but this still allowed multiple transactions to insert data
Good 'ol WITH(TABLOCKX) :)
Unless there's another way to block inserts, without blocking everything
else?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUnggGA1FHA.268@.TK2MSFTNGP09.phx.gbl...
> For INSTEAD OF triggers, use the ol' @.@.IDENTITY instead:
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> PRINT 'T-SQL: B ->' + ISNULL( CAST( @.@.IDENTITY AS VARCHAR(11)) ,'<NULL>' )
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
> news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
how
insert
>|||> But that requires an exclusive table lock to stop people putting in more
> data :)
Hmm, not sure I understand. Why are you saying that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551dbd$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
> Ok,
> But that requires an exclusive table lock to stop people putting in more
> data :)
> I tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,
> but this still allowed multiple transactions to insert data
> Good 'ol WITH(TABLOCKX) :)
> Unless there's another way to block inserts, without blocking everything
> else?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uUnggGA1FHA.268@.TK2MSFTNGP09.phx.gbl...
> how
> insert
>|||Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does not
require locks, it is scoped to a single session.
The best thing to do is to use your other key (you should have one because
the surrogate key (the identity value) should be a surrogate for something,
otherwise you have a potential mess) to fetch the value:
insert into dbo.tblTest --probably stop prefixing tables with tbl too :)
values...
select ID -- generally better to name <tablename>Id so they are easier to
implement/use as foreign keys
from tblTest
where keycolumn<s> = @.valueYouEntered
If you want it changed in the future, please go to:
http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
And vote for this!
Thanks!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
> Here's a fun question :)
> If I have an instead of trigger on a table, which replaces the insert, how
> can I get the identity insert from the data inserted?
>
> -- example --
> IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
> CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
> CLUSTERED , Data1 CHAR(1) NOT NULL )
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
> PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
>
> GO
> CREATE TRIGGER
> TR_dbo_tblTest
> ON
> dbo.tblTest
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
> BEGIN
> INSERT INTO
> dbo.tblTest ( Data1 )
> SELECT
> Data1
> FROM
> inserted
> PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
> END
> GO
>
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> -- The following should return 2, but it returns NULL because the insert
> was done by the trigger.
> PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
> '<NULL>' ) -- NULL
>|||> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
> not require locks, it is scoped to a single session.
Oh yeah, the reason why you might not be able to use this would be if a
trigger inserted data into another table.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
> not require locks, it is scoped to a single session.
> The best thing to do is to use your other key (you should have one because
> the surrogate key (the identity value) should be a surrogate for
> something, otherwise you have a potential mess) to fetch the value:
> insert into dbo.tblTest --probably stop prefixing tables with tbl too :)
> values...
> select ID -- generally better to name <tablename>Id so they are easier to
> implement/use as foreign keys
> from tblTest
> where keycolumn<s> = @.valueYouEntered
> If you want it changed in the future, please go to:
> http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
> And vote for this!
> Thanks!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
> news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
>|||Yeah I just remembered,
The auditing tables also have an identity field :)
fun \o/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:e4ckAmB1FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Oh yeah, the reason why you might not be able to use this would be if a
> trigger inserted data into another table.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
convincing."
> (Oscar Wilde)
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
because
to
http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
> ----
--
2
insert
>|||Yes you're right of course it's session based,
I've been playing with mysql too much at the wend :o
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
not
> require locks, it is scoped to a single session.

scope_identity()

I have this foreign function

Code Snippet

create function fillmuon1(jdbc ds)->boolean
as for each muon m
sqlu(ds," Insert into particle (id,eventid,px,py,pz,kf,ee) VALUES (" +
itoa(id(m)) + "," + itoa(id(event(m))) + "," + itoa(px(m)) + "," +
itoa(py(m)) + "," + itoa(pz(m)) + "," + itoa(kf(m)) + "," + itoa(Ee(m)) + ");
Insert into leptonaux(id) VALUES (SCOPE_IDENTITY());

Insert into muonaux(id) VALUES (SCOPE_IDENTITY());");

fillmuon1(:ds);

My problem when i call scope_identity() for the first time works, but when i call it the second time is trying to add a null value, so what can I do to keep the value

Yes. The idenity values will be reset when you call INSERT statement.

Change the code as follow as,

sqlu(ds," Insert into particle (id,eventid,px,py,pz,kf,ee) VALUES (" +
itoa(id(m)) + "," + itoa(id(event(m))) + "," + itoa(px(m)) + "," +
itoa(py(m)) + "," + itoa(pz(m)) + "," + itoa(kf(m)) + "," + itoa(Ee(m)) + ");
Declare @.ID as int; Set @.ID=SCOPE_IDENTITY(); Insert into leptonaux(id) VALUES (@.ID);

Insert into muonaux(id) VALUES (@.ID);");

SCOPE_IDENTITY()

is there an sql mobile equivalent of SCOPE_IDENTITY()?

SQL Mobile does support @.@.IDENTITY but there is no need for the concept of scope because there is no support for sprocs, triggers, etc so this distinction is meaningless.

Darren

|||

hello,

I would like to know how come I did try and I did not work for me

What I did was literary from the SQL Server Editor in Visual Studio type select @.@.Identity and it did not work for me

Thanks in advance

|||

Hello Nelson,

Execute that command on Pocket PC's Query Analyzer. It works.

Greetings.

scope_identity()

I have an ASP front end on SQL 2000 database. I have a form that submits to
an insert query. The entry field is an "identity" and the primary key. I
have used scope_identity() to display the entry# of the record just entered
on the confirmation page. Now I need to insert the entry into another
table. This is my query:

SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::', '::po_number::')
SELECT scope_identity() AS entry
INSERT INTO wo_combo_body
(entry) VALUES ('::entry::')
SET nocount off

This query displays the entry number of the record just entered, but inserts
a 0 in to entry field of the 2nd table. Any help would be great.

Thanks,
Darren>SELECT scope_identity() AS entry
does not assign the identity to a variable named entry, it just
returns a recordset like any other select

either
declare @.Entry int
set @.Entry = (select scope_identity())
insert into table (field) values (@.Entry)

or
insert int table (field) values (select scope_identity())

also move the set nocount off to the top

On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
<celtics@.lan-specialist.com> wrote:

>I have an ASP front end on SQL 2000 database. I have a form that submits to
>an insert query. The entry field is an "identity" and the primary key. I
>have used scope_identity() to display the entry# of the record just entered
>on the confirmation page. Now I need to insert the entry into another
>table. This is my query:
>SET NOCOUNT ON
>INSERT wo_main
> (site_id, customer, po_number)
>VALUES ('::site_id::', '::customer::', '::po_number::')
>SELECT scope_identity() AS entry
>INSERT INTO wo_combo_body
>(entry) VALUES ('::entry::')
>SET nocount off
>This query displays the entry number of the record just entered, but inserts
>a 0 in to entry field of the 2nd table. Any help would be great.
>Thanks,
>Darren|||Hi

If you can use a local variable to hold what is returned by SCOPE_IDENTITY.
This variable can then be used in the second insert statement. You may also
want to add some error checking! Rather than returning a result set it may
also be better(faster) to return the value as a parameter

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:aptZb.32366$um1.10431@.twister.nyroc.rr.com...
> I have an ASP front end on SQL 2000 database. I have a form that submits
to
> an insert query. The entry field is an "identity" and the primary key. I
> have used scope_identity() to display the entry# of the record just
entered
> on the confirmation page. Now I need to insert the entry into another
> table. This is my query:
> SET NOCOUNT ON
> INSERT wo_main
> (site_id, customer, po_number)
> VALUES ('::site_id::', '::customer::', '::po_number::')
> SELECT scope_identity() AS entry
> INSERT INTO wo_combo_body
> (entry) VALUES ('::entry::')
> SET nocount off
> This query displays the entry number of the record just entered, but
inserts
> a 0 in to entry field of the 2nd table. Any help would be great.
> Thanks,
> Darren|||Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
any pitfalls with doing it with a trigger?

Also...

On the same confirmation page I want to diplay links to a page for each
table. Basically I need to select the entry field from each table that I
have inserted to with the trigger. I can then use this as a hyperlink to
the each page. Any ideas on this?

"Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> >SELECT scope_identity() AS entry
> does not assign the identity to a variable named entry, it just
> returns a recordset like any other select
> either
> declare @.Entry int
> set @.Entry = (select scope_identity())
> insert into table (field) values (@.Entry)
> or
> insert int table (field) values (select scope_identity())
> also move the set nocount off to the top
> On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> <celtics@.lan-specialist.com> wrote:
> >I have an ASP front end on SQL 2000 database. I have a form that submits
to
> >an insert query. The entry field is an "identity" and the primary key.
I
> >have used scope_identity() to display the entry# of the record just
entered
> >on the confirmation page. Now I need to insert the entry into another
> >table. This is my query:
> >SET NOCOUNT ON
> >INSERT wo_main
> > (site_id, customer, po_number)
> >VALUES ('::site_id::', '::customer::', '::po_number::')
> >SELECT scope_identity() AS entry
> >INSERT INTO wo_combo_body
> >(entry) VALUES ('::entry::')
> >SET nocount off
> >This query displays the entry number of the record just entered, but
inserts
> >a 0 in to entry field of the 2nd table. Any help would be great.
> >Thanks,
> >Darren|||Hi

You have very little scope to insert new records in a secondary table if you
use a trigger, as you can not pass parameters to it. If there is only one
column in the second table it should be redundant. If there are other
columns then you should write a stored procedure and use a transaction to
maintain consistency see books online :
BEGIN TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ba-bz_96zy.htm
ROLLBACK TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ra-rz_471q.htm
COMMIT TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_7w6m.htm

You should be able to identify the values inserted in a session by including
identifying data in the table such as Username, Datatime, SessionId etc.

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:bnxZb.71491$n62.1826@.twister.nyroc.rr.com...
> Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
> any pitfalls with doing it with a trigger?
> Also...
> On the same confirmation page I want to diplay links to a page for each
> table. Basically I need to select the entry field from each table that I
> have inserted to with the trigger. I can then use this as a hyperlink to
> the each page. Any ideas on this?
>
> "Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
> news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> > >SELECT scope_identity() AS entry
> > does not assign the identity to a variable named entry, it just
> > returns a recordset like any other select
> > either
> > declare @.Entry int
> > set @.Entry = (select scope_identity())
> > insert into table (field) values (@.Entry)
> > or
> > insert int table (field) values (select scope_identity())
> > also move the set nocount off to the top
> > On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> > <celtics@.lan-specialist.com> wrote:
> > >I have an ASP front end on SQL 2000 database. I have a form that
submits
> to
> > >an insert query. The entry field is an "identity" and the primary key.
> I
> > >have used scope_identity() to display the entry# of the record just
> entered
> > >on the confirmation page. Now I need to insert the entry into another
> > >table. This is my query:
> > > >SET NOCOUNT ON
> > >INSERT wo_main
> > > (site_id, customer, po_number)
> > >VALUES ('::site_id::', '::customer::', '::po_number::')
> > >SELECT scope_identity() AS entry
> > >INSERT INTO wo_combo_body
> > >(entry) VALUES ('::entry::')
> > >SET nocount off
> > > >This query displays the entry number of the record just entered, but
> inserts
> > >a 0 in to entry field of the 2nd table. Any help would be great.
> > > >Thanks,
> > >Darren
>

scope_identity()

I have four tables:
1- customer details
2- bank details
3- car details
4- contract details

All these tables are linked with the contract ID which is the primary key in table 4 and foriegn key in the rest. When a new customer inputs all the above data from the VB front, I want table 4 to give contract ID with a autonumber, which should be sent to the other tables, so that the contract in all tables are the same so that it is linked properly....

I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager....

Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both

THANKSPLEASE HELP...I'm new to db design and really need this for my application to work....

THNAK YOU|||(...)
I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager....
I don't think you can use the EM for calling SCOPE_IDENTITY(). Besides, I think you'll need @.@.Identity instead. How is the data stored? All from VB? Do you use a stored procedure?

(...)
Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both
Which one is unique?|||Yeah all the data is inputted through VB. I previously tried to join the 4 tables into a view and after all the data was entered (and so the contract ID would be the same in each table)...however you can not update a view like that!!!

Would it work if I joined the tables using a stored procedure...how would I use @.@.idenity....what I want it to do is eg,

in Contract table contract ID 4 = customer ID 1
then in the customer table contract ID 4 should be respresently by customer 1 personal details|||If a new row is inserted into a table with the identity column, @.@.identity holds the latest identity value inserted, fe:
set nocount on

create table tab1 (myint integer identity (10,1), myvar varchar(10))
go

insert into tab1 (myvar) values ('aa')
select * from tab1
select @.@.identity

insert into tab1 (myvar) values ('aa')
select @.@.identity

go

select * from tab1
go

drop table tab1
go

I think there might be an alternative for the hughe lump of a join trying the update all tables all at once (which I think won't work anyway): I'm thinking of updating/inserting each table seperately from the others and have a commit/rollback to have either the fresh data committed on success or rollbacked in case of failure. See BOL for commit examples.|||You need to actually change Kaiowas example back to SCOPE_IDENTITY(). People should really stop using @.@.identity. If you have a trigger on your table that inserts into another table with an identity column, you just captured the identity column of the table in the trigger instead of the one you meant to capture. This is the difference between @.@.identity and SCOPE_IDENTITY().

Basically, in your stored procedure, insert into contract details, and SET @.variable = SCOPE_IDENTITY(), which will be the IDENTITY you just inserted into contract details. Then populate the other tables with the values they need. If you need to, have seperate stored procedures. Return the @.variable to your application as the output of the first insert. Use that in your future inserts.

You should be doing everything through stored procedures btw.|||Anyone want to mention that "prepopulating" tables is a bad idea?

Are you collecting all the data at once?

Is there a 1 to many relationship?

Don't you have something more meaningful to identify a customer?|||Also, think about this...when you want to look up a customer in the future, what are you going to use to identify them as the correct customer?

Probably from a pick list right?

Whatever you see on that list that makes you know it the right person...that's your key...

Not some bs number...

It's like...

ok here's a list of arbitray numbers...pick the right one...no way

Should add this to the list

The Devils Spawn (http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx)|||You need to actually change Kaiowas example back to SCOPE_IDENTITY(). People should really stop using @.@.identity. If you have a trigger on your table that inserts into another table with an identity column, you just captured the identity column of the table in the trigger instead of the one you meant to capture. This is the difference between @.@.identity and SCOPE_IDENTITY().
And to think how often I actually used @.@.identity... lucky for me there's no triggering here, it's all in sp's!|||The customer has a customer ID which is used to differentate between each user. However, the four tables are linked through a contract ID, which is the pk (and autonumber) in contract table. When a new contract is created the data is stored over four tables... I want to be able to ensure the contract ID given in the contract table (autonumber) is copied to the other three pages....

How do I populate the other tables with the scope_identity i.e. the contract ID (autonumber) ??|||you could put it in a local variable, fe:

declare @.my_identity as integer

insert into tab1 (myvar1) values ('example')
set @.my_identity = scope_identity()

insert into tab2 (myint2, myvar2) values (@.my_identity, 'scope_identity')

You may want to add some error checking as well, change the set into a select allowing multiple variables to be filled in one statement. See @.@.error in BOL for this.

Unless there's this thing called scope_error() or @.@.scope_error, er, oh no wait, maybe not.. ugh.. or perhaps you do. Oh brother!

Well, I guess I shouldn't have started reading about that Surrogate Key-article Brett was so kind to point out, I think I'll be running for politics now.|||I gotta ask...

Are all the columns in the other tables nullable?

If ContractId is a FK, and part of a composite PK, what the other non nullable PK compononent?

Drumroll please......

SCOPE_IDENTITY()

hi

what is difference between thos two's

SCOPE_IDENTITY()

and

@.@.IDENTITY

thanx

@.@.identity returns the LAST used identity value. It's not neccessarily the Scope value.

This example should explain it.

Code Snippet

create table t1(i int identity(1,1), j int)
create table t2(i int identity(100,10), j int)
go

create trigger _tr on t1
for insert
as
if @.@.rowcount=0 return;
insert t2(j)
select j from inserted;
go

insert t1(j) values(1)

select *, @.@.identity [@.@.ident], scope_identity() [scope]
from t1
go

drop table t2,t1
go

|||

thanx for reply.

u mean @.@.identity will return last value which is inserted into table t2... where as SCOPE_IDENTITY() will return the value of same table in this case t1

|||

Yup! Scope_identity() was invented to _correct_ the flaw of @.@.identity.

Think about it. If you were to enter an order into the Orders table. Wouldn't you want to know the last OrderID? @.@.identity will give you the wrong OrderID if there is a trigger that happens to insert into another table that has an identity column. Scope_identity() will guarantee that you get the correct OrderID.

|||

yes u are rite

thanx a lot

SCOPE_IDENTITY()

Trying to insert record in Parent table and return value of that rows IDENTITY, to pass back to app for use inserting child rows into other table. Looks clean against tutorial samples(http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/050207-1.aspx). Works with Insert until *** lines are added - HELP?

ERROR:

Msg 201, Level 16, State 4, Procedure qc_submitInsertQCparentReturningID_2, Line 0

Procedure or function 'qc_submitInsertQCparentReturningID_2' expects parameter '@.newQCparent_ID', which was not supplied.

Based on this Procedure:

ALTER PROCEDURE qc_submitInsertQCparentReturningID_2

(@.newQCparent_ID INT OUTPUT) ***

AS

-- Insert New QCparent RETURNING qcParentID

INSERT INTO app.dbo.a1_qcParent

([rowCreatedBy]

,[rowNotes]

,[rowLastAction]

,[rowLastActionBy]

,[rowLastActionNote])

-- Using Parameter Variables

VALUES('Anonymous Submit'

,'By qc_submitInsertQCparentReturningID'

,'Insert'

,'Guest'

,'show donald')

-- Read the qcParient_ID back for Items2fix Insert

SET @.newQCparent_ID = SCOPE_IDENTITY() ***

- Try New SPROC

EXEC qc_submitInsertQCparentReturningID_2

Thanks, unfortunately neither worked...

snippet 1 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.newQCparent_ID):

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@.newQCparent_ID".

snippet 2 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=scope_identity())

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

|||

Sorry, I got it wrong; Leave your original procedure code as it was. Change your call of the stored procedure from

Code Snippet

EXEC qc_submitInsertQCparentReturningID_2

to

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

and if you wish to see the results something like:

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

select @.outputParm as [@.outputParm]

|||

Parameters are by default NULLable in SQL Server. But in case of OUTPUT parameters, you will have to always pass a value. So if you want to ignore the output value you can do:

exec qc_submitInsertQCparentReturningID_2 NULL;

If you need to retrieve the output value then do:

declare @.id int;

exec qc_submitInsertQCparentReturningID_2 @.id OUTPUT;

|||

THat allowed it to run, insert was successful, but the following resulted in 9 as scope_Identity but 14 rows listed:

- Try New SPROC

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

a) I was expecting to see the same scope as the last inserted rows ID (14) ?

and

b) can I use the @.id to populate another procedure insert ?

|||Removed the GO between statements, but Scope_Identity remained 9 while rows increased|||

In the statement:

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

The SCOPE_IDENTITY() function will not work as you are thinking -- because the IDENTITY column is assigned inside of the stored procedure and what happened there is NOT in the scope of the CALLING query. As I said above, you will need to do something like

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

Select @.id

Select * from a1_qcParent

SCOPE_IDENTITY()

is there an sql mobile equivalent of SCOPE_IDENTITY()?

SQL Mobile does support @.@.IDENTITY but there is no need for the concept of scope because there is no support for sprocs, triggers, etc so this distinction is meaningless.

Darren

|||

hello,

I would like to know how come I did try and I did not work for me

What I did was literary from the SQL Server Editor in Visual Studio type select @.@.Identity and it did not work for me

Thanks in advance

|||

Hello Nelson,

Execute that command on Pocket PC's Query Analyzer. It works.

Greetings.

SCOPE_IDENTITY()

Hello altogether, my problem ist that I get following error message:

Create Stored Procedure
Unable to chances to the stored procedure.
Error Details:
'Scope_Identity' is not a recognized function name.

This is my Stored Procedure:

CREATE PROCEDURE sp_HyperSoftCustomer
@.Name varchar(25),
@.Adress varchar(250)
as
insert into HyperSoftCustomer(Name, Adress, Date)
values (@.Name, @.Adress, GetDate())
Select SCOPE_IDENTITY()
GO

I am using MSDE - MSSQLServer

I hope there is anybody who can help me?

Thanks, mexx

Hmmmm, that works fine for me. Are you using MSDE 2000?|||

Hi, I am working with

SQL Distributed Management Framework (SQL-DMF)
Microsoft Server Service Manager
Version 7.00.623

My System is Windows Home Edition and I simulate the Internet Information Services IIS (from Windows Professionel Edition) with the Web Matrix Webserver.

Is something missed? Is there a problem of the configuration?

Regards, mexx

|||

mexx:

Hi, I am working with

SQL Distributed Management Framework (SQL-DMF)
Microsoft Server Service Manager
Version 7.00.623

Accoring toHow to identify your SQL Server version, you are running a version of SQL Server 7. MSDE 1.0 runs on the SQL Server 7 engine. SCOPE_IDENTITY was not available in this version; use @.@.IDENTITY instead.

I suggest you install MSDE 2000, which uses the SQL Server 2000 engine. You can get it from here: Installing MSDE for ASP.NET Web Matrix.

SCOPE_IDENTITY( ) in distributed transaction [:S]

i am inserting new record in linked server and i need to get the id (which is of course autonumber) of newly added record. can't i get it using SCOPE_IDENTITY( ) ? SCOPE_IDENTITY( ) seems to be returning null. so SCOPE_IDENTITY( ) doesn't work in distributed transaction?

Hi Keeara,

As far as I know, the SCOPE_IDENTITY() can return the identity in a distributed transaction if the column in the table has been set as an identity.

The column has to be a number or the identity will not be returned.

|||

apparently, it returns NULL. i tried adding record in TEST table in distributed transaction and got NULL.

is there any way to get recently added identity in distributed transaction?

any help will be appreciated.

SCOPE_IDENTITY with ASP

I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY. It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.

What I'm seeing is an intermittent problem where the foreign key in the
second table is not what it should be. I think the problem may be due
to the fact that the insert into the first table and the calling of
SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
in between.

Is it possible that 2 users may be calling my ASP page at the same time
and causing a concurrency problem due to the INSERT and the
SCOPE_IDENTITY being done in 2 different SQL statements? I read that
SCOPE_IDENTITY always returns the last identity value generated from
"the current connection", so I thought that would mean that it wouldn't
get messed up by another ASP request. But now I'm thinking that
perhaps ASP uses connection pooling which could mean that 2 users could
be sharing the same connection which would cause this concurrency
issue.

Does anyone know if my theory of what's wrong is plausible?hmm.. you got me here

Cowly the Game player

Please click on my links
http://spacefed.com
http://gc.gamestotal.org
http://uc.gamestotal.org
http://aw.gamestotal.org
http://www.gamestotal.org
http://3700ad.gamestotal.com
http://www.spacefederation.net
http://www.gamestotal.org/news/
http://ballmonster.gamestotal.com
http://www.spacefederation.net/manual/
http://gc.gamestotal.org/i.cfm?p=aboutgc
http://uc.gamestotal.org/i.cfm?p=aboutgc
http://www.gamestotal.org/corp/
http://www.gamestotal.org/strategygames/|||"Larry" <larry_grant_dc@.hotmail.com> wrote in message
news:1114610604.033965.138570@.o13g2000cwo.googlegr oups.com...
>I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY. It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be. I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements? I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request. But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.
> Does anyone know if my theory of what's wrong is plausible?

You don't mention if you're using stored procedures, but your description
seems to suggest you aren't. SCOPE_IDENTITY() returns the last value
inserted "within the current scope" - if you do your INSERTs in a stored
proc, then the proc itself is the scope, so there is no problem with
concurrency. But if you're executing each SQL statement directly, then there
could be a concurrency issue because all statements using the same
connection would share the same scope.

Apart from this issue, using stored procedures is generally a good idea, for
a number of security and performance reasons:

http://www.sommarskog.se/dynamic_sql.html#Why_SP

Simon|||I am not using stored procs.

I still have a question about your statement "there could be a
concurrency issue because all statements using the same connection
would share the same scope".

The connection is created within my ASP page. If several users call
the ASP page at the same time, is that considered the "same connection"
or different connections?|||"Larry" <larry_grant_dc@.hotmail.com> wrote in message
news:1114627612.035136.42880@.z14g2000cwz.googlegro ups.com...
>I am not using stored procs.
> I still have a question about your statement "there could be a
> concurrency issue because all statements using the same connection
> would share the same scope".
> The connection is created within my ASP page. If several users call
> the ASP page at the same time, is that considered the "same connection"
> or different connections?

If IIS opens only one connection to the server, then yes, that would all be
in the same scope. If each execution of the ASP page opens a new connection
to MSSQL (which seems unlikely to me, but I know almost nothing about ASP),
then they would be in different scopes. You can use sp_who2 to view the
current connections, and also see sysprocesses and @.@.SPID in Books Online.

If you're unsure about how ASP is managing connections, you'll probably get
better feedback in an ASP forum, although from a purely SQL perspective, I
suspect that using a stored proc should solve the issue anyway.

Simon|||Larry (larry_grant_dc@.hotmail.com) writes:
> I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY. It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be. I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements? I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request. But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.

I don't know ASP, but what is important is that you cannot use a
model where you connect for each query here, but you must use the
same connection for the two queries, so that you retain scope.

But it may be easier to send the SELECT satement as part of the
INSERT batch to save a round trip.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp