July 24, 2003 at 11:24 am
I work with a commercial product that implements a table with a unique INT key that is not an identity column. When the application adds records to this table, it does the adds one-by-one and uses a separate table (one row, one column) to implement a 'pop queue' to retrieve a unique number. (See below for an simplified illustration of how this works in TSQL.)
The problem with this arrangement is that we regularly have to import a large number of rows into this table outside of the application. Frequently, the source is just another table somewhere, so doing this as an INSERT INTO ... SELECT FROM ... is very appealing. However, I've never been able to figure out a way to generate unique number as part of the SELECT FROM ... statement.
Invariably, we have to write a cursor or WHILE statement that 'pops' new values off the UserNumDB table and inserts the rows individually into the UserIDs table. Not only is this a pain in the neck, it's also many times slower than a set-type operation. We can't easily drop the constraint because it's a foreign key for many other tables.
My preference would be to have some mechanism that would yield increasing INT values just like single-insert mechanism below, although this isn't actually a product requirements (in other words, the INT values could be random, but they do have to be unique obviously). I'm also not hung up on incremementing the queue value after each row -- these mass updates are done while no one else is on the system, so we can just update the queue with the last value at the end. Can anyone offer some guidance on this?
Sample code to illustrate this problem is below.
Thanks,
Mark Denner
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[UserIDs]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [UserIDs]
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[UserNumDB]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [UserNumDB]
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[SourceTable]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [SourceTable]
CREATE TABLE UserIDs
(
UserNum Int NOT NULL,
UserName VarChar(64) NOT NULL
CONSTRAINT [PK_UserIDs] PRIMARY KEY CLUSTERED
(
[UserNum]
)
)
CREATE TABLE UserNumDB
(
UserNum Int NOT NULL
)
CREATE TABLE SourceTable
(
UserID VarChar(64)
)
--Start the queue
INSERT INTO UserNumDB (UserNum) VALUES(1)
--Insert a single row: this works fine
DECLARE @UserNum Int
SELECT @UserNum = UserNum FROM UserNumDB
INSERT INTO UserIDs (UserNum, UserName) Values(@UserNum, 'JSMITH')
UPDATE UserNumDB SET UserNum = @UserNum + 1
--Now try a set operation. First, populate SourceTable:
INSERT INTO SourceTable VALUES('KSMITH')
INSERT INTO SourceTable VALUES('LSMITH')
INSERT INTO SourceTable VALUES('MSMITH')
INSERT INTO SourceTable VALUES('NSMITH')
--This won't work! The MAX value is only calculated when the query starts, so all
--records try to insert with a UserNum of 2
INSERT INTO UserIDs SELECT
(SELECT MAX(UserNum) + 1 FROM UserIDs),
UserID
FROM SourceTable
Edited by - mdenner on 07/24/2003 11:25:37 AM
Edited by - mdenner on 07/24/2003 11:26:56 AM
July 24, 2003 at 12:54 pm
I'd insert into a temp table that does use an identity. You can set the seed to be the "pop" value from your separate table and then update that at the end. Be sure you lock this value until then (use a transaction). Insert into your table from the temp table..
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply