Solved

Autoincrement inserts in SQL with NO identity key

Posted on 2002-07-11
10
542 Views
Last Modified: 2011-10-03
-- 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,
@count int,
@max 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
--this load.

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
BEGIN
        INSERT INTO equipment_category_lkup
        (category_id, category_name)
     SELECT @min as new_catid, 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 AND @min <= @max

        SELECT @min = @min + 1
-- tried Set with @@rowcount here as well.

END

/*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?*/
0
Comment
Question by:mgkelso
  • 5
  • 4
10 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
The INSERT statement will insert all new records at one time, in one batch -- that's why they all have the same @id value.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You should be able to limit the INSERTs to one at a time by changing the statement as follows:

INSERT INTO equipment_category_lkup
       (category_id, category_name)
    -- added "TOP 1" to next line to limit INSERT to 1 row
    -- at a time
    SELECT TOP 1 @min as new_catid, 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
    --AND @min <= @max  this isn't really needed

Also, you need to verify whether the WHILE needs to be "@min < @max" or "@min <= @max".

   
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
As long as you have no performance issues with doing the INSERTs one at a time, I think this should work for you.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Upon further thought ...

Uh oh, that suggestion will keep selecting the same row over and over as the rows aren't being deleted :=(.  To make it work you would have to delete the rows or change the NULL value.

You are limiting the category_name to NULL and yet are matching on category_name ??  Something looks like flawed logic here.
0
 

Author Comment

by:mgkelso
Comment Utility
The matching condition on production category name is required if you want to retrieve and insert only the new category names into production.  The new load may have a category name that already exists in production. Don't want to load the possible dups and the new list doesn't have id's to join on...the whole point of the script.  The null filter on the production category name is required to limit the list to only the new categories to load that have no id...



0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mgkelso
Comment Utility
Thanks for your help Scott. I'm at my wits end on this one so if you have any more ideas, send them in.  There must be a simple SQL answer without having to build a cursor and loop thru it for each new category...
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
OK, I understand the part about not having a name, but then why does the query also match on name?  Shouldn't it just check for NULL values without matching?  Was that part just copied from another query?
0
 

Author Comment

by:mgkelso
Comment Utility
Check the table create statements and possibly generate a few rows of dummy data.  New categories have no id.  Its just a 1 to N list of category names that may or may not exist in production.  I must show all of the new category names from the left, i.e., equipment_category_new table (based on the left outer join on category name) but return only the new names (not all rows) that do NOT exist in production based on the where condition.  If you don't have the where condition, you'll retrieve all rows from the new list or if the where condition is not null, you'll retrieve all the rows from production that equal the new list.  We only want the new category names that have no id yet that don't exist in prod...

Sorry so wordy!



0
 
LVL 32

Accepted Solution

by:
bhess1 earned 200 total points
Comment Utility
Ummm... a cursor seem like exactly what you need here:

DECLARE @newCatID int

SELECT @newCatID =     MAX(category_id) + 1 FROM equipment_category_lkup
--set the first new entry equal to maximum categories in prod + 1

Declare @CatName Varchar(30)

Declare c_Work CURSOR AS
SELECT 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

Open c_Work
FETCH NEXT FROM c_Work INTO @CatName
WHILE @@FETCH_STATUS = 0
BEGIN
       INSERT INTO equipment_category_lkup
       (category_id, category_name)
       VALUES (@newCatID, @CatName)
       SET @NewCatID = @NewCatID + 1
       FETCH NEXT FROM c_Work INTO @CatName
END

Close c_Work
Deallocate c_Work

---------------------------------

Another way would be to use a temp table with an autoinc, like this:

Declare @currMax int
SELECT @currMax =     MAX(category_id) + 1 FROM
equipment_category_lkup

CREATE TABLE #tmpWork (category_id int identity, Category_Name varchar(30))

INSERT INTO #tmpWork (category_name)
    SELECT 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


INSERT INTO equipment_category_lkup
       (category_id, category_name)
    SELECT category_id + @currMax, category_name
    FROM #tmpWork

DROP TABLE #tmpWork


----------------------

Either of these should work.
0
 

Author Comment

by:mgkelso
Comment Utility
That solved the problem!  You were very precise with the answer and provided an alternative to the cursor solution.  Exactly what I was looking for.  Thank you.

~cheers,
Michael
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now