autoincremental field

How can we give serial numbers to the records in a tableUse RowNumber() function. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3quq.asp?frame=true
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23sgjfUJcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> How can we give serial numbers to the records in a table
>

Autogenerating Numbers for a primary key field, "studyId," in a tablebut with a few

I have a question on autogenerating numbers for a primary key field, "studyID," in a table—but with a few twists.

We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).

Here’s the range of values we want our StudyIDs to be (this is to be our validation rule as well):

10001-19999 for Site 1

20001-29999 for Site 2

Your suggestions are VERY VERY WELCOME! THANKS!

If all sites were in seprate databases or at least tables it would be easy just set "identity increment" =1 and "identity seed" = [side prefix]0001 during table creation, but you need maintain all of this in one table so probably trigger is the only solution.

Tomek

|||

This is the fundamental problem with autoincrementing pk fields. You can't really do this in one table. You could create 2 tables and use the post above, then join them into 1 table for output; but, realistically you can't accomplish this goal in 1 table with 1 autogenerated number with arbitrary insert order and more than one logical grouping of keyspaces.

It is generally suggested that if you can possibly avoid it, you should not use autoincrementing pk fields -- but rather use primary keys or clustered keys based on the content of the data itself if you can guarantee uniqueness. If you cannot, it may be advantageous in the long run to avoid duplicate rows by using a count in your table.

e.g. First Last

John Gordon

John Gordon

becomes

First Last Count

John Gordon 2

In the short run, you could split your table into two and join them to report, but in the long run, you may want to consider the limitations this design imposes on the index space of your data and the possibility for growth.

Hope that helps,

John

autogenerate numbers from 000001 to 999999

I would like the numbers 000001 to 999999 to autogenerate in a new
database. I will be transfering information from another database and
in that database the numbers 000001 to 010000 are already taken. They
are used as identifiers in other programs and it would be easier if
they were stored as written. Using identity the 0's are eliminated.
Is there a way to keep them?

Thank you,
MIf you're happy to generate IDs one at a time, try this:

CREATE TABLE Sometable (col1 CHAR(6) PRIMARY KEY CHECK (col1 LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9]'), col2 VARCHAR(10) NOT NULL)

INSERT INTO Sometable (col1, col2)
SELECT RIGHT('000000'+CAST(
COALESCE(CAST(MAX(col1) AS INTEGER),0)+1 AS VARCHAR(6)),6),
'Blah Blah'
FROM Sometable

--
David Portas
----
Please reply only to the newsgroup
--|||Thank you very much. It occurred to me after reading your post that I
didn't need to do any math with the numbers, so I defined them as
varchar(6) and wrote a little program in vb.net with some loops to
fill them in for me. It worked out very well.
-M

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<Qf2dnfksRaR5GO2iRVn-vw@.giganews.com>...
> If you're happy to generate IDs one at a time, try this:
> CREATE TABLE Sometable (col1 CHAR(6) PRIMARY KEY CHECK (col1 LIKE
> '[0-9][0-9][0-9][0-9][0-9][0-9]'), col2 VARCHAR(10) NOT NULL)
> INSERT INTO Sometable (col1, col2)
> SELECT RIGHT('000000'+CAST(
> COALESCE(CAST(MAX(col1) AS INTEGER),0)+1 AS VARCHAR(6)),6),
> 'Blah Blah'
> FROM Sometable

Auto numbers

How can I make my form to create an autonumber to my table when I insert a new record. Is there any field in the table similar to autonumber field in MSACCESS. Please help!You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks I am going to try it.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks you really help me. Now I find another problem. When I am in my main page I got links for administrator, clients, ect... this links are for entering data, delete and update. If I am in the clients area for example, if I view a record information and then go to anoter record the page does not displays, I got to go to my home page and then go to the page that I was looking and it displays the information. Hope you can help me whis this one.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||well, you have to tell us how you wrote the code to your application,
else we will be firing blank gueses to your question.

Although I do that often :)

What programing language are u using? ASP vbscript? and how do you query your database ? via ADO ?

Things like this will help us help u.

Auto Incrementing field

I have a asp.net form which is for orders with a field for numbers in it.
What I want is when a user raises a new order the Order number field will
be populated automatically with the next order number. I want this order
number to be incremented on the database table. The order number is of the
form 05/001 and when the next order is raised it will be 05/002. 05 is the
year.
Anyone any ideas how to do this.
Thanks.
Message posted via http://www.webservertalk.comHi
I'd recommend you to create a table with the following structure
CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
OrderDate DATETIME
)
Now you can easily to extract the order number along its orderdate.
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||here OrderID can me an IDENTITY column.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Uri Dimant" wrote:

> Hi
> I'd recommend you to create a table with the following structure
> CREATE TABLE Orders
> (
> OrderID INT NOT NULL PRIMARY KEY,
> OrderDate DATETIME
> )
> Now you can easily to extract the order number along its orderdate.
>
> "macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
> news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>
>|||Hi,
Look into the identity property in books online.
Thanks
Hari
SQL Server MVP
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||Why?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:F05619B5-D96F-405E-B385-466995B09A71@.microsoft.com...
> here OrderID can me an IDENTITY column.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Uri Dimant" wrote:
>
message
it.
will
order
the
the|||Although it has been suggested, an Identity column would not satisfy your
requirement of "YY/SSS".
I would recommend a stored procedure:
PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
The procedure would generate a new Order_Num, insert a row into your Orders
table (and whatever else tables), and return the order number as an output
param.
If the Order_Num needs to be known when an order is first created, run the
procedure first, fill in your field, and set the Order_Status to incomplete.
When the submit the order, change the status.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"macca via webservertalk.com" wrote:

> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||I think this may cause concurency issue.
You can use an IDENTITY column plus another YEAR column (varchar).
whenever you show it to user, you just need to concat these 2 columns.
However, since YY is always current year, you can choose not to store this
value unless this column can be changed in the future.
Hope this may help.
Thanks.
Leo Leong
"Alex Papadimoulis" wrote:
> Although it has been suggested, an Identity column would not satisfy your
> requirement of "YY/SSS".
> I would recommend a stored procedure:
> PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
> The procedure would generate a new Order_Num, insert a row into your Order
s
> table (and whatever else tables), and return the order number as an output
> param.
> If the Order_Num needs to be known when an order is first created, run the
> procedure first, fill in your field, and set the Order_Status to incomplet
e.
> When the submit the order, change the status.
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "macca via webservertalk.com" wrote:
>

Auto increment from 1 to ......7million

Friends,
I have created a new column called ID on my table. I need
to populate it with numbers from 1 to 7 million plus. so
like 1,2,3,4,5,6,.... Increments of one. Can you please
tell me what script can do an auto increment insert into
this column? Thank you.
Mary.In EM, right click on table and table design, choose the new column and set
identity with seed =1, increament = 1. It will be auto filled with integers
starting from 1.
You can also try this:
declare @.int int
set @.int = 1
while @.int <= 7000000
begin
insert into tablename (id) values (@.int)
set @.int = @.int + 1
end
"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote in message
news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.|||I like this one.
use tempdb
go
set nocount on
create table test (col1 int, col2 int)
insert test values (0,1)
insert test values (0,5)
insert test values (0,5)
insert test values (0,5)
insert test values (0,5)
insert test values (0,6)
insert test values (0,6)
insert test values (0,6)
declare @.x int
set @.x = 0
update test
set @.x = col1 = @.x + 1
select * from test
drop table test
Mary Jassy wrote:
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.|||Thank you very very much.
Imma
>--Original Message--
>In EM, right click on table and table design, choose the
new column and set
>identity with seed =1, increament = 1. It will be auto
filled with integers
>starting from 1.
>You can also try this:
>declare @.int int
>set @.int = 1
>while @.int <= 7000000
>begin
>insert into tablename (id) values (@.int)
>set @.int = @.int + 1
>end
>
>"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote
in message
>news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
>> Friends,
>> I have created a new column called ID on my table. I
need
>> to populate it with numbers from 1 to 7 million plus.
so
>> like 1,2,3,4,5,6,.... Increments of one. Can you
please
>> tell me what script can do an auto increment insert into
>> this column? Thank you.
>> Mary.
>
>.
>|||Mary,
Just curious but of what use would a table such as this be?
--
Andrew J. Kelly SQL MVP
"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote in message
news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.

Auto increment from 1 to ......7million

Friends,
I have created a new column called ID on my table. I need
to populate it with numbers from 1 to 7 million plus. so
like 1,2,3,4,5,6,.... Increments of one. Can you please
tell me what script can do an auto increment insert into
this column? Thank you.
Mary.
In EM, right click on table and table design, choose the new column and set
identity with seed =1, increament = 1. It will be auto filled with integers
starting from 1.
You can also try this:
declare @.int int
set @.int = 1
while @.int <= 7000000
begin
insert into tablename (id) values (@.int)
set @.int = @.int + 1
end
"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote in message
news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.
|||I like this one.
use tempdb
go
set nocount on
create table test (col1 int, col2 int)
insert test values (0,1)
insert test values (0,5)
insert test values (0,5)
insert test values (0,5)
insert test values (0,5)
insert test values (0,6)
insert test values (0,6)
insert test values (0,6)
declare @.x int
set @.x = 0
update test
set @.x = col1 = @.x + 1
select * from test
drop table test
Mary Jassy wrote:
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.
|||Thank you very very much.
Imma

>--Original Message--
>In EM, right click on table and table design, choose the
new column and set
>identity with seed =1, increament = 1. It will be auto
filled with integers
>starting from 1.
>You can also try this:
>declare @.int int
>set @.int = 1
>while @.int <= 7000000
>begin
>insert into tablename (id) values (@.int)
>set @.int = @.int + 1
>end
>
>"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
need[vbcol=seagreen]
so[vbcol=seagreen]
please
>
>.
>
|||Mary,
Just curious but of what use would a table such as this be?
Andrew J. Kelly SQL MVP
"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote in message
news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.

Auto increment from 1 to ......7million

Friends,
I have created a new column called ID on my table. I need
to populate it with numbers from 1 to 7 million plus. so
like 1,2,3,4,5,6,.... Increments of one. Can you please
tell me what script can do an auto increment insert into
this column? Thank you.
Mary.In EM, right click on table and table design, choose the new column and set
identity with seed =1, increament = 1. It will be auto filled with integers
starting from 1.
You can also try this:
declare @.int int
set @.int = 1
while @.int <= 7000000
begin
insert into tablename (id) values (@.int)
set @.int = @.int + 1
end
"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote in message
news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.|||I like this one.
use tempdb
go
set nocount on
create table test (col1 int, col2 int)
insert test values (0,1)
insert test values (0,5)
insert test values (0,5)
insert test values (0,5)
insert test values (0,5)
insert test values (0,6)
insert test values (0,6)
insert test values (0,6)
declare @.x int
set @.x = 0
update test
set @.x = col1 = @.x + 1
select * from test
drop table test
Mary Jassy wrote:
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.|||Thank you very very much.
Imma

>--Original Message--
>In EM, right click on table and table design, choose the
new column and set
>identity with seed =1, increament = 1. It will be auto
filled with integers
>starting from 1.
>You can also try this:
>declare @.int int
>set @.int = 1
>while @.int <= 7000000
>begin
>insert into tablename (id) values (@.int)
>set @.int = @.int + 1
>end
>
>"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote
in message
>news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
need[vbcol=seagreen]
so[vbcol=seagreen]
please[vbcol=seagreen]
>
>.
>|||Mary,
Just curious but of what use would a table such as this be?
Andrew J. Kelly SQL MVP
"Mary Jassy" <anonymous@.discussions.microsoft.com> wrote in message
news:25dc01c4702c$e993b560$a301280a@.phx.gbl...
> Friends,
> I have created a new column called ID on my table. I need
> to populate it with numbers from 1 to 7 million plus. so
> like 1,2,3,4,5,6,.... Increments of one. Can you please
> tell me what script can do an auto increment insert into
> this column? Thank you.
> Mary.

Auto generating numeric column

Hi There,

I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.

AutoRow
1
2
3
4 --- reset
1
2
3
4 ---reset

Any help would be great,

many thanks

Stuart

An indentity field is going to require unique values, so reseting it isnt going to work, your going to throw an SQL Exception.

Are you trying to accomplish this just on the SQL server, or can you do it as part of your application?

|||Unfortuanatley it needs to be done on the server|||

Check this sample to see whether it helps:

DROP TABLE tbl_testIden
go

CREATE TABLE tbl_testIden (id int identity(1,1), name sysname)
GO

CREATE TRIGGER trg_tsetIden ON tbl_testIden FOR INSERT,UPDATE
AS
IF (IDENT_CURRENT('tbl_testIden')>=4)
DBCC CHECKIDENT('tbl_testIden',RESEED,0)
GO

DECLARE @.i INT
SET @.i=1
WHILE (@.i<100)
BEGIN
INSERT INTO tbl_testIden(name) SELECT 'Person#'+CONVERT(VARCHAR(8),@.i)
SET @.i=@.i+1
END
go
SELECT * FROM tbl_testIden

|||

Hi There,

I managed to get it working using the Row_Number function. It has some very useul derivetives also.

Row_Number simply creates and incremental list which is reset when thepartiation by value changes.

ROW_NUMBER()OVER(PARTITIONBY ReqPointIdORDERBY ReqPointIdDESC)AS RowGroup

I'll look at your approach as it alows me to specify and upper limit.

Many thanks for your time

Regards

Stuart

|||

You are looking for a combination of ROW_NUMBER and %, but without more of the query, I can't help you. Generically...

SELECT (RowGroup % 4)+1 As AutoGen, more columns here

FROM (

SELECT ROW_NUMBER() OVER (something) AS RowGroup, more columns here

) t1

You may even be able to combine the two, but I'm not familiar enough with ROW_NUMBER to say that would work, but if it does... Then...

SELECT (ROW_NUMBER() OVER (something) % 4)+1 AS RowGroup, more columns here

FROM somewhere

should work as well. I think most people (myself included) were confused because you said you wanted an autogenerated column. That tends to lead people to believe you are speaking of a table column, not a field in a resultset.