create sequence

is there any function in tsql similar to sqls function create sequence?

Luis:

If you can logically order your records you ought to be able to apply the ROW_NUMBER() OVER( ORDER BY the field))

For instance, if you have a table "A" that has an integer record key "aKey" you can generate sequence numbers something like this:

Code Snippet

select aKey,

row_number() over(order by aKey)

as SEQ

from A

|||

Kent post is applicable to SQL Server 2005 . if its sql 2000, you can try Identity Column or identity function which is also applicable in 2005 . Read about these in BOL (books online)

From BOL

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'employees') DROP TABLE employees GO EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true' SELECT emp_id AS emp_num, fname AS first, minit AS middle, lname AS last, IDENTITY(smallint, 100, 1) AS job_num, job_lvl AS job_level, pub_id, hire_date INTO employees FROM employee M adhu|||-edited--

I have this schema

Code Snippet

CREATE Table particle (
idap INT not null,
id INT,
eventid INT not null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

ALTER TABLE particle
ADD CONSTRAINT pk_particle PRIMARY KEY (idap);

GO
Create Table Leptonaux(
id INT not null);

GO

ALTER TABLE leptonaux
ADD CONSTRAINT pk_leptonaux PRIMARY KEY (id);

GO

Alter table leptonaux
ADD Constraint leptonId FOREIGN KEY (id)
REFERENCES particle (idap) ON DELETE CASCADE;

GO

Create Table Muonaux(id INT not null);

GO

ALTER TABLE Muonaux
ADD CONSTRAINT pk_Muonaux PRIMARY KEY (id);

GO

Alter table Muonaux
ADD Constraint muonId FOREIGN KEY (id)
REFERENCES particle (idap) ON DELETE CASCADE;

GO

Create Table electronaux(
id INT not null);

GO

ALTER TABLE electronaux
ADD CONSTRAINT pk_electronaux PRIMARY KEY (id);

GO

Alter table electronaux
ADD Constraint electronId FOREIGN KEY (id)
REFERENCES particle (idap) ON DELETE CASCADE;

GO

Create Table jetaux(
id INT not null);

GO

ALTER TABLE jetaux
ADD CONSTRAINT pk_jetaux PRIMARY KEY (id);

GO

Alter table jetaux
ADD Constraint jetId FOREIGN KEY (id)
REFERENCES particle (idap) ON DELETE CASCADE;

GO

the problem is I filling using a jdbc from another application moving the data from a foreign database.

so, in the the foreign database I make a select * from jetb to move the data to this one and i call it in this way, the proble is idap is an artificial id that doesnt exist in the original schema, so thas why i need the sequence, and then I need a way to get the next value of the sequencia to make a Insert into particle and then an Insert into jetaux with the same sequence number, and the same process with muonaux and electronaux

|||manually i do it in this way

Code Snippet

Insert into particle (idap,Id,eventid,Kf,Px,Py, Pz, Ee)
VALUES(9,2,24455,11,-0.227242,-10.493,-28.8285,30.6796);

Insert into leptonaux(Id)
VALUES(9);

Insert into electronaux (Id)
VALUES(9);

but in i need to create a for each function that use the sequence and I know that in Mimer SQL that is posible, but i dont know how to do it here

in amossql it have to be something like this way

Code Snippet

for each electron e
sql(
Insert into particle (idap,Id,eventid,Kf,Px,Py, Pz, Ee)
VALUES(next value of sequence,id(e),id(event(e)),kf(e),px(e),py(e),pz(e),ee(e));

Insert into leptonaux(Id)
VALUES(same value of sequence);

Insert into electronaux (Id)
VALUES(seme value of sequence);)
);

note that inside sql() function is a string the tsql query (im avoiding some needed code just to make it easy to understand).

next value of sequenca and same value of sequence are the fields that i need to know how to implement
|||

Code Snippet

CREATE Table particle (
idap INT IDENTITY(1,1) not null,
id INT,
eventid INT not null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

Insert your data into all the fields of the particle table except idap.

(ie. insert into particle values(1, 101, 123, 456, 789, 543, 210))

idap will automatically create a sequential counter.

Then when all the data is in the particle table, copy the idap values into your other tables.

Code Snippet

INSERT INTO leptonaux (id)

select distinct idap from particle

repeat for the other tables.

|||

How do you think the solution by IDENTITY() and SCOPE_IDENTITY(), like below.

Code Snippet

CREATE TABLE particle

(

idap int IDENTITY(1,1)

, ....

)

and

Code Snippet

for each electron e

sql

(

Insert into particle (idap,Id,eventid,Kf,Px,Py, Pz, Ee)
VALUES(next value of sequence,id(e),id(event(e)),kf(e),px(e),py(e),pz(e),ee(e));

Insert into leptonaux(Id) VALUES(SCOPE_IDENTITY());

Insert into electronaux (Id) VALUES(SCOPE_IDENTITY());

)

|||the daleJ is the most close solution for me, but the problem rirght now is that for example 1,2,3,4 are muon, 5,6,7 are electron and 8,9,10 are jet for example, but all the particles have to be of one and only one type
|||THX Yosihiro, Im gonna try that one
|||

I don't quite follow that.

Can you clarify a little more?

|||

for some reason its not working, but could be a problem with JDBC, but do I need to put something in next value of sequence?

Code Snippet

for each electron e

sql

(

Insert into particle (idap,Id,eventid,Kf,Px,Py, Pz, Ee)
VALUES(next value of sequence,id(e),id(event(e)),kf(e),px(e),py(e),pz(e),ee(e));

Insert into leptonaux(Id) VALUES(SCOPE_IDENTITY());

Insert into electronaux (Id) VALUES(SCOPE_IDENTITY());

)

|||it was one sintax mistake, now its working.. thanks for the help