Insert a Row Number to a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert a Row Number to a field

Author

Topic

PeeJay2583
Starting Member

40 Posts

Posted - 2011-08-01 : 08:28:22

I have two tables tb1 and tb2. I append the data from tb2 to tb1

tb1 has fields Name, Emp ID, Row_No
tb2 has fields Name, Emp ID

which append query should i use so that i get an incremental row number whenever data is appended from tb2 to tb1 as tb1 doesn't has
a Row_No.

I want row number to be added like first record is 1 second record is 2 and so on.

Any help would be appreciated.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-08-01 : 09:16:37

If its SQL 2005 or above, use ROW_NUMBER()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 09:44:18

you mean continue the row_number from tab1? or generate new sequence for ones coming from tb2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PeeJay2583
Starting Member

40 Posts

Posted - 2011-08-01 : 10:04:31

I mean continuing Row Number from tb1.

One Option I found of Auto Increment field Row ID

Altered Row ID Field as IDENTITY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 12:40:40

no need. for that use statement like:-


INSERT INTO tb1 
SELECT Name,EmpID,(SELECT MAX(Row_No) FROM tb1) + ROW_NUMBER() OVER (ORDER BY EmpID)
FROM tb2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources