How do you use scope_identity()?

[iwm]

I need to insert a row into table "A". I was instructed to Select scope_identity() to create the ID for the new row in table "A". So I ran the following statement:

SELECT SCOPE_IDENTITY() from A

This statement returned a NULL value for each row. How do you use scope_identity to create an ID?

Thank you in advance for your assistance!

Ingrid

 

[bborissov]

SCOPE_IDENTITY() returns last IDENTYTY() created in the current batch.
It does not create anything.

DECLARE @Test TABLE (Id int IDENTITY(1,1), Fld1 varchar(20))
INSERT INTO @tEST (Fld1) VALUES ('asdasd')
SELECT SCOPE_IDENTITY()
INSERT INTO @tEST (Fld1) VALUES ('asdasd')
SELECT SCOPE_IDENTITY()
INSERT INTO @tEST (Fld1) VALUES ('asdasd')
SELECT SCOPE_IDENTITY()
INSERT INTO @tEST (Fld1) VALUES ('asdasd')
SELECT SCOPE_IDENTITY()

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Olaf Doschke]

To CREATE an ID you create an int field you set up as an IDENTITY field in the table definition, as Borislav showed, eg

CREATE TABLE employees
(
 id_num int IDENTITY(1,1),
 fname varchar (20),
 lname varchar(30)
);

Now once you INSERT INTO employees ('Borislav','Borrissov') the id_num field is automatically filled.

IDENTITY(1,1) is the typical definition, starting with 1, incrementing by 1.

SCOPE_IDENTITY() now returns the last generated IDENTITY value, but it does not generate an id. You can use it to determine the last generated id, if you want to use that id in child records as foreign key, otherwise you can just rely on a valid unique sequential number having been created.

There are slight differences between SCOPE_IDENTITY() @@IDENTITY and IDENT_CURRENT(). It's valuable knowing these difference, so read about those three. But for ID generation you need the INDENTITY clause of CREATE TABLE field clauses.

Once you created such an int IDENTITY field via code, open the table in design mode in sql server management studio and you'll see what properties of a field to set to get there with that designer.

Bye, Olaf.

 

[jbenson001]

a

 

[jbenson001]

If you want to create a new random ID for each row, then use NewID:

   SELECT NEWID from A

However, if you are creating a primary key for the table, I would use an Identity column.