Scope parameter: identity

Showing posts with label identity. Show all posts

Wednesday, March 28, 2012

Script to reset the "Identity Seed" and "Identity Increment" values

hi,

Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.

I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.

The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?

Any help whould be greatly appreciated.

Thanks,
Prashanth ReddyOriginally posted by Prashanth
hi,

Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.

I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.

The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?

Any help whould be greatly appreciated.

Thanks,
Prashanth Reddy

You have 2 options that spring to mind:

1. You can use TRUNCATE TABLE to accomplish this, but you will lose all data and as its a non-logged operation nothing will be written to the transaction log. I should add that if the table is referenced by a foriegn key constraint then you will not be able to use this method.

Usage: TRUNCATE TABLE tablename

2. The other option is to use DBCC CHECKIDENT to reset the seed:

Usage: DBCC CHECKIDENT (tablename, RESEED, 0)

macka.|||thanks for the response.|||thanks for the response.

I have patient table with PatientID as auto increment field
with
"Identity Increment" as 1 and
"Identity seed" as 1
I can chane Identity Seed using
DBCC checkident (patient,reseed,4)
How do i change the "Identity Increment" value to 10?

Prashanth|||The following script should work. In this example it resets the seed to 4 and increment to 10.

If the table is referenced by foreign key constraints then you will need to drop them before this script is run and add them back after it completes.

ALTER TABLE Demo
DROP CONSTRAINT PK_Demo

ALTER TABLE Demo
DROP COLUMN DemoID

ALTER TABLE Demo
ADD DemoID INT IDENTITY(4,10)

ALTER TABLE Demo ADD CONSTRAINT
PK_Demo PRIMARY KEY CLUSTERED
(
DemoID
) ON [PRIMARY]

macka.

Friday, March 23, 2012

Script to alter Identity, Identity Seed, Identity Increment

Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
>> Because I have to do this more then once for several Tables, I do not
>> want to do this manually but through a Script.
>> The thing is: do not have a clue how to do it.
>> Cheers
>> Marcel
>>
>> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
>> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>>
>

Script to alter Identity, Identity Seed, Identity Increment

Tuesday, February 21, 2012

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() 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() 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 w

end :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 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()

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) ***

-- 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

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;

Select SCOPE_IDENTITY()

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;

Select SCOPE_IDENTITY()

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 vs @@IDENTITY

Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY value
that another row could have been inserted into the same table by another user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.
@.@.IDENTITY and SCOPE_IDENTITY are session specific, so you will never see
identity values generated by other concurrent users. But the difference
between the two is explained in SQL Server Books Online documentation of
SCOPE_IDENTITY
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that
between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY
value
that another row could have been inserted into the same table by another
user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.
|||If I might add a note... There is a time when @.@.identity can give you the
incorrect information, and that is when there are triggers on the table...
So you are correct in your thinking about scope_identity - it is the one you
should probably be using...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
> Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
> level of isolation than @.@.IDENTITY? That is, is it ever possible that
> between
> inserting a row into a table and subsequently retrieving the @.@.IDENTITY
> value
> that another row could have been inserted into the same table by another
> user
> (even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
> to
> me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
> affected by the above scenario.
|||If u perform INSERTING, and u have TRIGGER that also perform some INSERT
so @.@.IDENTITY will be of table that TRIGGER inserts
Message posted via http://www.sqlmonster.com

SCOPE_IDENTITY vs @@IDENTITY

Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that betwee
n
inserting a row into a table and subsequently retrieving the @.@.IDENTITY valu
e
that another row could have been inserted into the same table by another use
r
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem t
o
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.@.@.IDENTITY and SCOPE_IDENTITY are session specific, so you will never see
identity values generated by other concurrent users. But the difference
between the two is explained in SQL Server Books Online documentation of
SCOPE_IDENTITY
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that
between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY
value
that another row could have been inserted into the same table by another
user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.|||If I might add a note... There is a time when @.@.identity can give you the
incorrect information, and that is when there are triggers on the table...
So you are correct in your thinking about scope_identity - it is the one you
should probably be using...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
> Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
> level of isolation than @.@.IDENTITY? That is, is it ever possible that
> between
> inserting a row into a table and subsequently retrieving the @.@.IDENTITY
> value
> that another row could have been inserted into the same table by another
> user
> (even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
> to
> me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
> affected by the above scenario.|||If u perform INSERTING, and u have TRIGGER that also perform some INSERT
so @.@.IDENTITY will be of table that TRIGGER inserts
Message posted via http://www.droptable.com

SCOPE_IDENTITY vs @@IDENTITY

Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY value
that another row could have been inserted into the same table by another user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.@.@.IDENTITY and SCOPE_IDENTITY are session specific, so you will never see
identity values generated by other concurrent users. But the difference
between the two is explained in SQL Server Books Online documentation of
SCOPE_IDENTITY
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that
between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY
value
that another row could have been inserted into the same table by another
user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.|||If I might add a note... There is a time when @.@.identity can give you the
incorrect information, and that is when there are triggers on the table...
So you are correct in your thinking about scope_identity - it is the one you
should probably be using...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
> Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
> level of isolation than @.@.IDENTITY? That is, is it ever possible that
> between
> inserting a row into a table and subsequently retrieving the @.@.IDENTITY
> value
> that another row could have been inserted into the same table by another
> user
> (even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
> to
> me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
> affected by the above scenario.|||If u perform INSERTING, and u have TRIGGER that also perform some INSERT
so @.@.IDENTITY will be of table that TRIGGER inserts
--
Message posted via http://www.sqlmonster.com

scope_identity and sessions

I found in bol that I can use scope_identity to get the last identity in a specific session and for a the current scope. Now what I would like to know is what is a session? We usually create one user for our software in the database and create an application users table. This means that all users actually connect as the same database user to the database. Does SQL Server see this as different sessions or just one?True what you have mentioned above sessions, check with BOL for sessions then.|||Even if you log in using same username, you can open multiple sessions, like in Query Analyzer.

SCOPE_IDENITY() - Incorrect Value (Returned)

There are two tables

Table_1:

ProductID (Identity Increment)

ProductDescription (nvarchar)

Table_2:

ProductID (int)

I have a sql statement and it's like this. This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:

DECLARE@.getTheNewProductIDintINSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;SET@.getTheNewProductID =SELECT SCOPE_IDENTITY()
INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)

What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2

The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.

How do you do this?

I did try using @.@.identity which may have stuffed things up??

Thanks for your help Smile

Try with this code.

DECLARE@.getTheNewProductIDint

INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;

SET@.getTheNewProductID = (SELECTProductID from Table_1 where ProductID= SCOPE_IDENTITY())

INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)

Wednesday, March 28, 2012

Script to reset the "Identity Seed" and "Identity Increment" values

Friday, March 23, 2012

Script to alter Identity, Identity Seed, Identity Increment

Script to alter Identity, Identity Seed, Identity Increment

Tuesday, February 21, 2012

SCOPE_IDENTITY() vs. @@IDENTITY

SCOPE_IDENTITY() problems

SCOPE_IDENTITY() and "instead of" Triggers.

scope_identity()

SCOPE_IDENTITY()

SCOPE_IDENTITY()

SCOPE_IDENTITY vs @@IDENTITY

SCOPE_IDENTITY vs @@IDENTITY

SCOPE_IDENTITY vs @@IDENTITY

scope_identity and sessions

SCOPE_IDENITY() - Incorrect Value (Returned)

Scope parameter

Blog Archive

About Me