Similar to Sequence in SQL Server.

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 2000 Forums

SQL Server Development (2000)

Similar to Sequence in SQL Server.

Author

Topic

krishnan_sn
Starting Member

14 Posts

Posted - 2005-09-22 : 03:24:18

Hi,

In Oracle we can create sequence like

CREATE SEQUENCE seq
MINVALUE 1
START WITH 1
INCREMENT BY 1

and use this as

INSERT INTO TableName
(id, name)
VALUES
(seq.nextval, 'Name');

This can be useful when you need to create a unique number to act as a primary key.

Is there any thing available in MSSQL similar to Oracle sequence?
I know the use of Identity field. In Sql Server it automatically inserts the seed value. But in my case it is a non identity column, but the values has to be in incremental order and i have to add it in the Sql insert statement.

Regards
Krishnan

S.Navaneetha Krishnan

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 04:02:25

You will probably have to insert into a temporary table [with an IDENTITY column] first, and then insert into the actual table.

Only other alternative is to insert first with no value, and then UPDATE to set the value incrementally, but if you need to rely on a specific order that is a bit fraught - it is only like to be guaranteed with a temporary table - so back to square one!

To use an UPDATE to set an ascending set of numbers you'd need to do something like this:


DECLARE @MyCounter int
SET @MyCounter = 0

UPDATE MyTable
SET    @MyCounter = MyColumn = @MyCounter + 1

or possibly

UPDATE MyTable
SET    MyColumn = @MyCounter,
       @MyCounter = @MyCounter + 1

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 04:38:03

Or
Find maximum value of that column and increase it by 1

Declare @id int

Select @id=max(id) from yourTable
if @id is null 
	Set @id=1
else
	Set @id=@id+1

Insert into yourTable values(@id,'Name')

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 04:58:44

Blast! I thought it was a multi-row INSERT, based on Maddy's idea you could probably do this:


INSERT INTO TableName
(
	id, name
)
SELECT	(SELECT COALESCE(max(id), 1) FROM TableName),
	'Name'

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 05:12:20

Neat and Perfect Kris
Will COALESCE take advantage over ISNULL in this case?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 06:57:31

"Will COALESCE take advantage over ISNULL in this case"

COALESCE is Standards compliant. Plus I hate ISNULL - its the wrong name for a function as to me it implies that it returns True/False depending on some test - like IsNumeric or IsDate ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources