How to create order IDs - SQL Server Forums

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)

How to create order IDs

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-02-14 : 12:32:43

Hi
Imagine this scenario.

I want to enter order details into a table. The order ID increments from the previous order id. For instance, if the last order ID is 1000, the id for a new order would become 1001.

How do I do this. The way I see is:
Before entering the order details, get the last order ID, and increment by 1. Use this value as the order ID for the new order.

If this the way, can someone write me SQL for getting info from the last row.

You may tell me to use 'Identity' for the OrderID column, but that would start like 0, 1, 2 etc

I have spent long time writing this message, was not sure how to word it properly.

cas_o
Posting Yak Master

154 Posts

Posted - 2005-02-14 : 12:47:28

Identity does not need to start at 0, when you set a column as identity in Enterprise manager you can set a Seed value and an increment, so you could start at seed 1000 and increment say 10, so

create table orders(orderid int identity(1000,10), order varchar(10))
go

insert into orders(order)
values('order one')
values('order two')

select * from orders

result set

orderid Order
-----------------
1000 order one
1010 order two

;-]... Quack Waddle

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-02-14 : 12:53:19

IF you are just using an INT Field you could.

Insert Into Yourtable
OrderID = MAX(orderid) + 1
From Yourtable

Jim
Users <> Logic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-02-14 : 17:03:25

thanks, this is very useful info

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources