Use Sequence number in INSERT statement .

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

Use Sequence number in INSERT statement .

Author

Topic

Amit1987
Starting Member

3 Posts

Posted - 2011-05-17 : 03:17:18

Hi ,
i have create sequence in SQL server 2008 ( name GetNewSeqVal_Test) which is associated to TEST table ( QUERY : create table Test (
SeqID int identity(1,1) primary key,SeqVal varchar(1))).

I am able to view sequence value increment but now i would like to use same generated sequence number as value to some other table's column( TABLE1(T1)).

something similar to ORACLE as follows :
insert into TABLE1 values (SEQUENCE_1.nextval)

Can anyone tell me how to do same in SQL SERVER 2008 ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-17 : 03:54:46

how did u created sequence in SQL server 2008 ???

Amit1987
Starting Member

3 Posts

Posted - 2011-05-17 : 04:04:19

Using below Table & procedure

create table Test (
SeqID int identity(1,1) primary key,SeqVal varchar(1)))

create procedure GetNewSeqVal_Test
as
begin
declare @NewSeqValue int
set NOCOUNT ON
insert into Test (SeqVal) values ('a')
set @NewSeqValue = scope_identity()
return @NewSeqValue
end

On execution you can see the result as increment in return value.

Any idea how to incoporate it in INSERT statement which we can do in ORACLE as mentioned in first post ?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-17 : 04:32:23

What's wrong with just putting the identity column on the table you're inserting and letting SQL handle it. 2008 doesn't have sequences, and using tables in an attempt to create one is rife with problems.

--
Gail Shaw
SQL Server MVP

Amit1987
Starting Member

3 Posts

Posted - 2011-05-17 : 04:49:16

Hi,
We cant modify structure of TABLE by adding IDENTITY ;
along side We are using this sequence generator in various places in our custom product and we would like to maintain the same sequence in this case where T1 column of TABLE gets next value.

Is it really not possible to achieve functionality of oracle NEXTVAL ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources