automatic sequence number by id

Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
Edgar
Hi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL, [Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:

> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why does this data need to be stored, when you could always retrieve Seq at
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>
|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David
|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:

>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>
|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
sql

automatic sequence number by id

Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
EdgarHi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL,
[Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:

> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why does this data need to be stored, when you could always retrieve Seq a
t
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you a
re
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need t
o
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:

>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
>

Automated procedures help

Anyone any tips for the most efficient way to automate procedures/sequence of queries in SQL server 2000?Set up a SQL Server Job through Enterprise Manager's SQL Server Agent.

blindman|||automate procedures and quries into stored procedures.

if you wish to schedule these tasks you can setup jobs to execute stored procedures or TSQL commnds or both.|||could triggers not be the answer........?|||Triggers are used when you want some event to occur every time data in a table is inserted, deleted, or modified.

For nightly batch processing, which is what I thought you were getting at, scheduled jobs are the most convenient method.

What kind of task are you trying to automate?

blindman|||Basically, I have a table ( or many tables) which are going to be updated several times a week, after which some queries will be run...............just need to automate this process......|||Actually..I think using schduled jobs and sprocs is my answer...do you tend to agree?|||Absolutely.

Auto Number Sequence

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)

Whats the easiest way to do this?

--1.

Alter TABLE Incident

DROP COLUMN RecordId

--2.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 3000

--or 3.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 1

You can look up this infomation Alter Table and Alter Column from Books Online.

|||

hi,

you can use the identity function

make use use of its parameter seed=3000 to start from 3000

here's the syntaxt

identity(seed, increament)

here's your sample code

use northwind

select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employees

select * from #temp

regards,

joey

|||A potentially more expressive function you could use in place of identity is the row_number.

select row_number over( partiton by ... order by ...) + startin_number,

One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||

hi if any body can tell me the solutions of my prob.

i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc

|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||

You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....

-- create a simple test table
create table testcounter (thefld int)
go

-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go

-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0

-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go

-- list the results
select * from testcounter

Auto Number Sequence

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)

Whats the easiest way to do this?

--1.

Alter TABLE Incident

DROP COLUMN RecordId

--2.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 3000

--or 3.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 1

You can look up this infomation Alter Table and Alter Column from Books Online.

|||

hi,

you can use the identity function

make use use of its parameter seed=3000 to start from 3000

here's the syntaxt

identity(seed, increament)

here's your sample code

use northwind

select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employees

select * from #temp

regards,

joey

|||A potentially more expressive function you could use in place of identity is the row_number.

select row_number over( partiton by ... order by ...) + startin_number,

One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||

hi if any body can tell me the solutions of my prob.

i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc

|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||

You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....

-- create a simple test table
create table testcounter (thefld int)
go

-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go

-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0

-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go

-- list the results
select * from testcounter