0

How can I get get generated ID from primary key and then add it to another table in ASP.NET via SCOPE_IDENTITY? For example:

enter image description here

enter image description here

Last generated ID on column NRRENDOR is number 26, I have deleted the rows. Now when I add datas to the database the nexy generated ID on NRRENDOR will be number 27. That number I want it to add to column NRD.

Aristos
  • 66,005
  • 16
  • 114
  • 150
aldoblack
  • 175
  • 3
  • 20
  • This have nothing to do with asp.net (or I have wrong ?) – Aristos Apr 17 '14 at 10:15
  • what do you mean by `add it to another table in ASP.NET`? what code you have tried? – Nitin Sawant Apr 17 '14 at 10:16
  • Have you tried the way described here? [link](http://stackoverflow.com/questions/15591713/correct-use-of-scope-identity-function-within-simple-stored-procedure) – d_z Apr 17 '14 at 10:20
  • @Aristos , well I am coding in ASP.NET so you can help me the code in SQL Server. – aldoblack Apr 17 '14 at 10:21
  • @Nitin Sawant , column NRRENDOR is in Table1 and column NRD is in Table2 . Noe the last generated ID from NRRENDOR which is in Table 1 is number 26, the next generated ID will be number 27. Number 27 I want to add it to column NRD which is in Table2. – aldoblack Apr 17 '14 at 10:21
  • possible duplicate of [Get last generated id asp.net](http://stackoverflow.com/questions/23107570/get-last-generated-id-asp-net) – Suvendu Shekhar Giri Apr 17 '14 at 10:25
  • Hey @aldoblack ! I noticed that you are asking the same question multiple times.. why so ? Duplicate Post: http://stackoverflow.com/questions/23107570/get-last-generated-id-asp-net – Suvendu Shekhar Giri Apr 17 '14 at 10:31
  • @Suvendu Shekhar Giri, Oh, it was not me. I believe it was my friend. We are using the same account. – aldoblack Apr 17 '14 at 10:33

2 Answers2

1

In your INSERT code, assuming it's in a stored procedure, using SCOPE_IDENTITY will get you the last identity that was inserted, which you can either reuse in the stored procedure or return to your app to use in another statement.

Some dummy SQL to demonstrate:

INSERT INTO NRRENDOR(SomeColumn) VALUES(1)

DECLARE @LastID int
// set @LastID to the last id inserted
SELECT @LastID = SCOPE_IDENTITY()

// to use in same procedure
INSERT INTO NRD (SomeColumn) VALUES(@LastID)

// to return it to code - or you could use an output parameter
SELECT @LastID
Tanner
  • 22,205
  • 9
  • 65
  • 83
  • @aldoblack does your insert actually work (add the new row) and is the primary key column actually set as an identity column too? – Tanner Apr 17 '14 at 10:32
  • Yes primary key column is set as an identity column. It works but it is added as NULL not as Last Generated ID. – aldoblack Apr 17 '14 at 10:47
0

What has this got to do with ASP.NET? SQL would suffice.

INSERT INTO [Table2]( NRD)
SELECT  MAX(NRRENDOR)
FROM    Table1
' WITH (ROWLOCK, XLOCK, HOLDLOCK)

The correct way to do it will be to use @SCOPE_IDENTITY after you perform insertion as Tanner suggested. It will be worth noting that there is another way to get the current identity, ie, IDENT_CURRENT. You can use it like this

SELECT IDENT_CURRENT('Table1') + 1 as Current_Identity

Please note this too

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

This comment from marc_s sums it all. How to get the next identity value from SQL Server

Community
  • 1
  • 1
naveen
  • 53,448
  • 46
  • 161
  • 251
  • It is primary key. If MAX number of a primary is 25 and last generated ID is 30, it will give me number 25 not 30. – aldoblack Apr 17 '14 at 10:30
  • @aldoblack: so your PK is not AUTO? I assumed so from the ` generated ID from primary key`. If its auto generated, the last id would be the max id provided you are not using a negative identity seed. – naveen Apr 17 '14 at 10:33
  • Yeas it is auto. But there are times when I delete the row. For example I delete the last 5 rows in the table, so primary key is decreased by 5. Let's say MAX number of my primary key is 30. But if I delete 5 last rows the MAX number is 25. But I do not want number 25. I want the LAST GENERATED ID which is number 30. – aldoblack Apr 17 '14 at 10:40