SQL Server Performance Forum – Threads Archive

Computing a "sequence" on a multi-column PK

I have to migrate some tables from one database to another, and compute the destination keys based on the "conceptual primary key".
For example: Original Tables
===============
CREATE TABLE Orders(
ID int identity(1, 1) not null,
OrderNum char(5) not null,
…
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED(ID)
) CREATE TABLE OrderLines(
ID int identity(1, 1) not null,
OrderID int not null,
…
CONSTRAINT [PK_OrderLines] PRIMARY KEY CLUSTERED(ID),
CONSTRAINT [FK_OrderLines_Orders] FOREIGN KEY (OrderID) REFERENCES Orders(ID)
) Destination Tables
==================
CREATE TABLE Orders(
OrderNum char(5) not null,
…
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED(OrderNum)
) CREATE TABLE OrderLines(
OrderNum int not null,
ID smallint not null
…
CONSTRAINT [PK_OrderLines] PRIMARY KEY CLUSTERED(OrderNum, ID),
CONSTRAINT [FK_OrderLines_Orders] FOREIGN KEY (OrderNum)
REFERENCES Orders(OrderNum)
) In this case, I must compute a sequential ID for each OrderLine like:
OrderNum, ID
A001,1
A001,2
A001,3
A002,1
A003,1
…. The way I see to solve this is to define a cursor and maintain the current ID for the OrderNum. Is there a faster way to solve this without resorting to cursors? The tables have about 1 million records and 3 million records. Thanks in advance,
AndrÃ© Cardoso

How about using BCP? _________
Satya SKJ

Would something like this work? INSERT newdb.db :confused:

rderLines (OrderNum, ID, …)
SELECT a.OrderID, (SELECT COUNT(*)
FROM olddb.db :confused:

rderLines b
WHERE a.ID > b.ID
AND a.OrderID = b.OrderID) + 1, …
FROM olddb.db :confused:

rderLines a I am not totaly sure I understand what you are asking, but I think this may work.

It works great.
I was afraid it would be slow or fill up the tempdb or the transaction log, but it was fine. Thanks for the help and have a nice weekend!
AndrÃ© Cardoso

Just a correction to the script: INSERT newdb.db :confused:

rderLines (OrderNum, ID, …)
SELECT
(select O.OrderNum from olddb.db :confused:

rders O where a.OrderID = O.ID),
(
SELECT COUNT(*)
FROM olddb.db :confused:

rderLines b
WHERE a.ID > b.ID
AND a.OrderID = b.OrderID
) + 1,
…
FROM olddb.db :confused:

rderLines a

You could simplify it a little more like this: INSERT newdb.db :confused:

rderLines (OrderNum, ID, …)
SELECT c.OrderNum, (SELECT COUNT(*)
FROM olddb.db :confused:

rderLines b
WHERE a.ID > b.ID
AND a.OrderID = b.OrderID) + 1, …
FROM olddb.db :confused:

rderLines a
INNER JOIN olddb.db :confused:

rders c
ON (a.OrderID = c.ID)

]]>