Auto increment number - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Auto increment number

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-12 : 17:55:47

How to add auto increment number other than IDENTITY? Because IDENTITY is using INTO, i don't want to use that.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-12 : 18:13:11

What do you mean IDENTITY is using INTO? If you're referring to SELECT/INTO, then you don't have to do that to use an identity column.

Here's a quick example:

CREATE TABLE t1 (c1 int identity(1, 1), c2 int)
INSERT INTO t1 (c2) VALUES (99)
INSERT INTO t1 (c2) VALUES (5)
SELECT * FROM t1
DROP TABLE t1

Notice the values of c1 in the output of my small example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-12 : 18:24:14

Hi,

I am using Select statement to select data from existing tables and I want the output of the select statement to have an identity or added rows which determined how many rows are there. Hope this makes clear..:)

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 18:25:32

Use ROW_NUMBER() Function available in SQL 2005 onwards.

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-12 : 18:25:50

I got it now..ROW_NUMBER is the solution..:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources