CODEWITHSUNDEEP
sql-server-2008t-sqlsql-update
ComfortablyNumb
ComfortablyNumb

Reputation: 1456

Update table with sequential ints

I have inherited a company database which issues an order number as a GUID. Not exactly user friendly when you want to quote your order number! So what I want to do is add a new int column and add a unique sequential order number to each existing order (which has been ordered by datetime).

It's a great idea, but I'm stuck on how to actually do it as an update query! Any help would be greatly appreciated.

Upvotes: 2

Views: 465

Answers (3)

joshp
joshp

Reputation: 1892

One way.

alter table t add order_id int identity

This will add an auto-incrementing int identity column. There is no guarantee as to the order in which the ids will be assigned to the existing rows, but a unique id will be assigned to each existing row. Each new row inserted after this change will get a new unique id.

Before applying this to a real application consider whether existing code will work with an identity column. Often this approach is a really harmless upgrade. Code that tries to insert an identity column fails, unless it uses set identity_insert. You can't remove the identity property without dropping the column.

To round this out might want a unique constraint on the new id, both for retrieval speed and to enforce uniqueness if the id column is ever updated.

Upvotes: 3

Aaron Bertrand
Aaron Bertrand

Reputation: 280350

Unfortunately if you just add an IDENTITY column to the table, the existing orders will not necessarily get the IDENTITY values assigned in order of the OrderDate, so they will be "out of order" if you wanted to assign order ID values based on order date (which seems logical). Quick example:

CREATE TABLE dbo.Orders
(
    OrderGUID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    OrderDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT dbo.Orders(OrderDate) VALUES
('20120101'), ('20120102'), ('20120103');
GO

ALTER TABLE dbo.Orders ADD OrderID INT IDENTITY(1,1);
GO
SELECT OrderID, OrderGUID, OrderDate = CONVERT(DATE, OrderDate) 
FROM dbo.Orders 
ORDER BY OrderDate;

Results (obviously yours will differ):

OrderID OrderGUID                            OrderDate
------- ------------------------------------ ----------
2       C5CE909E-0469-45AE-A828-647C7F54AA14 2012-01-01
1       70D8EEB1-FDA8-4E56-874F-771999C6DB84 2012-01-02
3       8E7B42C3-6C4D-4860-8A82-AFADDBA96A4A 2012-01-03

If this is not acceptable you should probably create a new table and insert all the old orders into it (at which point you can also drop the GUID column as I alluded to in my comment).

CREATE TABLE dbo.OrdersCopy
(
  OrderID INT IDENTITY(1,1) PRIMARY KEY,
  ... other columns ...
);

INSERT dbo.OrdersCopy (OrderDate, ... other columns ...)
SELECT OrderDate, ... other columns ...
FROM dbo.Orders 
ORDER BY OrderDate
OPTION (MAXDOP 1); -- single-threaded is important!

EXEC sp_rename 'dbo.Orders', 'OrdersOld', 'OBJECT';
EXEC sp_rename 'dbo.OrdersCopy', 'Orders', 'OBJECT';

(If you want to keep the old GUID for reference, temporarily, while you clean up other tables, that's probably fine, but you shouldn't make it auto-populate anymore, and you should plan to remove it since it's wide and redundant.)

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Add an identity column to your table and the numbering will be taken care of for you.

alter table YourTable add YourTableID int identity

Upvotes: 5

Related Questions