Solved

Autoincrement inserts in SQL with NO identity key

Posted on 2002-07-11
10
559 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7147256
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:Scott Pletcher
ID: 7147262
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:Scott Pletcher
ID: 7147269
As long as you have no performance issues with doing the INSERTs one at a time, I think this should work for you.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7147304
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
ID: 7147431
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
 

Author Comment

by:mgkelso
ID: 7147445
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:Scott Pletcher
ID: 7147449
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
ID: 7147543
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
ID: 7147655
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
ID: 7147938
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 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