SQL Get the row number of the inserted row

Question

I am trying to get the row number of an inserted record so I can use it for a select statement. What I am trying to accomplish is insert a person into one table, get that row number and then select something from another table where the row numbers match. Here is what I got so far:

INSERT INTO TableA Values (‘Person’)

Select timeToken
From 
(
    Select 
      Row_Number() Over (Order By tokenOrder) As RowNum
    , *
    From TableB WHERE taken = false
) t2
Where RowNum = (Row Number of Inserted Item)

How do I get the row number of the inserted item, I want to compare ids as some records might have been deleted so they would not match.

TABLEA Data (primary key is id)

id name
3 John
12 Steve

TABLEB Data (primary key is id)

id timeToken tokenOrder taken
2   1:00am    1          false
3   2:00am    2          false
5   3:00am    3          true
6   4:00am    4          false

My expect result when I insert person, the select take would return 4:00am

I am doing this in a stored procedure.

Uh... I think you may be slightly confused about how SQL Server stores data. A row doesn't inherently have a "row number". — ZLK, Apr 05 '17 at 04:13
*My expect result when I insert person, the select take would return 4:00am* still not clear add expected result also in table format — Pரதீப், Apr 05 '17 at 04:26
When I insert the value 'Person' to TableA, I am expecting the select to return 4:00am because 'Person' would be the 3rd row and 4:00am would also be the 3rd row as 3:00am taken value is false. — user979331, Apr 05 '17 at 04:31
TableA and TableB do not have any other relations except "row number"? Then, what happens when you delete 1 row in TableA or TableB? — TriV, Apr 05 '17 at 04:37
@TriV thats what I am trying to solve, should I have an relation between TableA and TableB? If so, what would it be?? — user979331, Apr 05 '17 at 04:39

score 3 · Accepted Answer · answered Apr 05 '17 at 04:14

3

It is an error to think that rows have numbers unless an ORDER BY clause is included.
The only way to find a row after you have inserted it is to search for it. Presumably your table has a primary key; use that to search for it.

answered Apr 05 '17 at 04:14

Ross Presser

6,027
1
34
66

Yes I primary key like an id, what do I do in the case of deleted records, TableA primary key column could start at 51 or 3 and that would not match the tokenOrder of TableB – user979331 Apr 05 '17 at 04:18
By the sounds of it you are trying to do something like what is listed on thhe following link [LINK](http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) Basically : 'INSERT INTO TableA (Person) OUTPUT Inserted.ID VALUES('bob');' – David Apr 05 '17 at 04:34
@David I've seen that, but what do I do in the case when a timeToken is false? example 3:00am is taken, so if even I restarted the primary key when finished, the id wont match the tokenOrder I am trying to get – user979331 Apr 05 '17 at 04:37
Oops, Sorry! I posted multiple times to the question. I meant to only post the one comment. I do not immediately have an answer for you. – David Apr 05 '17 at 04:43

score 1 · Answer 2 · answered Apr 05 '17 at 04:59

1

Try This .It may help you out

Declare @TableA_PK BIGINT

INSERT INTO TableA Values ('Person')

SET @TableA_PK=SCOPE_IDENTITY()

Select timeToken
From 
(
    Select 
      Row_Number() Over (Order By tokenOrder) As RowNum
    , *
    From TableB WHERE taken = false
) t2
Where RowNum =@TableA_PK

SCOPE_IDENTITY(): Scope Identity will captures the last inserted record primary key value and which can be stored in a varaible and and then it can be for further re-use

answered Apr 05 '17 at 04:59

Mahesh.K

901
6
15

but my ids may not be 1,2,3,4,5,6 it could be something like this 5,6,7,9,11,14,17 – user979331 Apr 05 '17 at 05:01
your table coulmn doesn't have the `IDENTITY` ..?? – Mahesh.K Apr 05 '17 at 05:18
timeToken in Table A? No – user979331 Apr 05 '17 at 06:10
I'm asking whether any of the column as Auto Increment (IDENTITY ON) feature on your table..? – Mahesh.K Apr 05 '17 at 06:40
Yes, my id column is auto incremetn primary key, but if I delete testing records, it will start out weird – user979331 Apr 05 '17 at 07:58
if your id column is auto incrementing and you want to display the data related to newly inserted record then the above query will help you. – Mahesh.K Apr 05 '17 at 08:36

score 0 · Answer 3 · edited May 23 '17 at 11:54

0

By the sounds of it you are trying to do something like what is listed on thhe following link LINK - SQL Server - Return value after INSERT

Basically :

INSERT INTO TableA (Person)
OUTPUT Inserted.ID
VALUES('bob');

edited May 23 '17 at 11:54

Community

1
1

answered Apr 05 '17 at 04:35

David

437
3
10

I've seen that, but what do I do in the case when a timeToken is false? example 3:00am is taken, so if even I restarted the primary key when finished, the id wont match the tokenOrder I am trying to get – user979331 Apr 05 '17 at 04:37

score 0 · Answer 4 · answered Apr 05 '17 at 05:26

0

Adding a foreign key constraint(referencing primary key in table A) in table b will be good since you won't be able to delete records from table A without deleting them from table B. It'll be helpful for comparing the records using ID.

answered Apr 05 '17 at 05:26

Coder1991

715
1
5
8

Can I see an example of this @Coder1991 – user979331 Apr 05 '17 at 06:09
@user979331 add a column id1 in table b using alter statement as below. alter table table b add id1 int null. Add foreign key constraint as given below. atler table table b add constraint fk_tbl_b foreign key(id1) references table A(id) – Coder1991 Apr 05 '17 at 06:14
I could be deleting from either one or in Table B, timeTokens could be marked as taken, which then I would not want to include them. – user979331 Apr 05 '17 at 06:56
@user979331 Are you going to perform delete operations on either of the two tables in Store procedure? If so what is the purpose of the delete operation. – Coder1991 Apr 05 '17 at 07:02
oh not in the store procedure. I will be deleting test records before hand and some timeTokens will be marked as taken. – user979331 Apr 05 '17 at 07:06
If you delete a record from table B then the same record (record in table A with same id) should get deleted from table A. If you create a foreign key constraint then it is not possible to delete records from table A (Parent table) unless the same records have been deleted from table B (Child table). – Coder1991 Apr 05 '17 at 07:09
If you have created these two tables (a and b) with parent and child relationships (Primary/Foreign key constraints) then it will be easier for you to delete records from both the tables without breaking the dependencies. – Coder1991 Apr 05 '17 at 07:16

Monjurul Haque Shah Masum · Answer 5 · 2017-04-05T05:47:23.277

0

Try this

declare @rowNum int;

INSERT INTO TableA Values ('Person')

SET @rowNum =SCOPE_IDENTITY()

select * from TableA  where id = @rowNum

edited Apr 05 '17 at 05:47

answered Apr 05 '17 at 05:44

Monjurul Haque Shah Masum

19
3

SQL Get the row number of the inserted row

5 Answers5