Autoincrement inserts in SQL with NO identity key
Posted on 2002-07-11
-- Here's the script to create tables referenced in sql script below:
CREATE TABLE EQUIPMENT_CATEGORY_NEW
(category_name varchar (30) NOT NULL,
CONSTRAINT PK_EC_NEW PRIMARY KEY (category_name))
CREATE TABLE EQUIPMENT_CATEGORY_LKUP
(category_id int NOT NULL, category_name varchar(30) NULL,
CONSTRAINT PK_EC_LKUP PRIMARY KEY (category_id))
/* This script below is trying to insert only the new equipment categories from the equipment_category_new table into the equipment_category_lkup table in a production database.
New categories (from the equipment_category_new table) have NO id assigned to them so the script tries to assign one id per new category based on the maximum category_id's already in production. These new records are supposed to be incremented upward by 1 to the maximum inserted, i.e., count of new categories + maximum
categories already in production but it doesn't work.
It seems the script always fails due to a pk violation by assigning the same id to each new record. So, the counter is not working in the script. Also, category_id is an int, NOT an identity column. We can't change it w/out breaking both the current application and the fk references to other dependency tables.*/
DECLARE @min int,
SELECT @min = MAX(category_id) + 1 FROM equipment_category_lkup
--set the min equal to maximum categories in prod + 1
SELECT @count = COUNT(equipment_category_new.category_name) FROM EQUIPMENT_CATEGORY_NEW LEFT OUTER JOIN EQUIPMENT_CATEGORY_LKUP ON
EQUIPMENT_CATEGORY_NEW.category_name = EQUIPMENT_CATEGORY_LKUP.category_name
WHERE EQUIPMENT_CATEGORY_LKUP.category_name IS NULL
--sets @count equal to the count of new categories for
SET @max = @min + @count
--sets @max equal to the maximum categories in production
--plus the count of new categories to add for the script
--to loop thru.
WHILE @min < @max
INSERT INTO equipment_category_lkup
SELECT @min as new_catid, category_name
FROM EQUIPMENT_CATEGORY_NEW LEFT OUTER JOIN
WHERE EQUIPMENT_CATEGORY_LKUP.category_name IS
NULL AND @min <= @max
SELECT @min = @min + 1
-- tried Set with @@rowcount here as well.
/*I tried changing the last select @min = @min + @@rowcount, which gives me the unique
list of new equipment category names to load but still assigns the same id to each record.
Using @min = @min + 1 gives me all the new categories to load but in groups up thru the max,
i.e, each group has a unique category name to load but with the same id within that group.
The next group has a new id and so on...
Help! What am I missing?*/