I think this is the query I arrived at for getting the next PK value.

select @currentval = max(Convert(int,substring(GenLevelAddFieldID,len(@@servername) + 1,10))) from tblLevelAddFieldRel where GenLevelAddFieldID like @@servername + '%'

Sergiy, as far as the bands. How could I use identity and keep with the rule of source bands.

Where source 1 data must have pk numbers between 1 and...

As far as the sequence number being a requirement in a table of a db we are in the process of re-designing. The business case is:

We have applications that...

Lynn, you have completely and utterly convinced me to look for a set based solution to the problems that come as part of the job.

Cudos for that!

STUFF

There is a lot of stuff I have read, and want to address them for those that are curious.

I have had extensive experience with set based queries in the past,...

Um I don't quite understand and it's my fault for not getting my last req accross.

Forget producttype..

lets say we just have partnumber, seq

and we want to insert a part at...

OK DAMN

I don't have it all wrong, but here is the issue.

The user defines the sequence, it isn't alphabetical. So when inserting a record the user would give 2 as...

let me give some data

partnumber, producttype, sequence

7401,belt,1

7432,belt,2

8401,hose,1

8422,hose,2

This is the table.

inert a record (8403,hose,?)

after a resequence you would have

8401,hose,1

8402,hose,2

8422,hose,3

let's say we have the table with

partnumber, producttype, sequence

After inserting part in the table we need to resequence the sequence number which depends on the partnumber.

so select partnumber from...

{} is much better than BEGIN BEGIN BEGIN BEGIN END END END END

J, my dba wife pointed it out to me last night that set based solutions are not always superior to cursors. It depends on the database, however for batch job...

Lynn, since you brought it up. I want to apologize for calling you dense.

It's been an adventure this week, I still don't have a computer from the recent crash and...

I agree. If I can do more stuff without the use of cursors, I am happier.

Some things just have to be looped. Like for instance, sequence numbers.

but we will get...

Given the above scripts:

here is the cursor script that will populate one of the tables.

This can easily be done with a setbase solution????????????

:w00t:

set nocount on

delete PrdAttribute

DBCC CHECKIDENT...

just a bit more complicated:

db scripts...

USE [PDS]

GO

/****** Object: Table [dbo].[PrdIchAttributeTypes] Script Date: 03/17/2009 17:21:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[PrdIchAttributeTypes](

[PrdIchAttributeTypeID] [int] NOT NULL,

[PrdIchAttributeType] [nvarchar](255)...