SQL Insert with Identity value AND incremental rollover column ?

Hi Experts:  I need to create a SQL insert query that will create new rows with a normal identity column, and in another integer column, increment (and rollover) a three-digit number.  I'm using MSSQL Server 2008 r2.  Can I do this in one step, or can I only do it with a separate Insert and an Update procedure?

Similar to the simplified code below, I have a table and I need to make an insert procedure that will increment the MBatch column from 1 to 999, then rollover back to 1 on the next row insert.  (So, the row with identity 999 will probably have MBatch = 999, and identity=1000 will have MBatch=1)
When I call the Insert procedure, I will pass it the "@MStatus" value, and would like the proc to return the Identity column value and the MBatch value.

Thanks.

CREATE TABLE [dbo].[MTRANS]
   ([MTRANS_ID] int IDENTITY (1, 1) NOT NULL,
    [MBatch]     int  NULL,
    [MStatus]    char(1) NULL
   )



CREATE PROCEDURE [prMCCARD_BATCH_INSERT]
   (@MTRANS_ID  int OUTPUT,
    @MStatus    char(1),
    @MBatch     int OUTPUT
   )
   	
As

BEGIN
   Begin
      INSERT INTO [CCARD_BATCH]([MStatus])
      VALUES (@MStatus)
      -- do magic here to increment/rollover MBatch ?		 
   End
	
   SELECT TRANS_ID = @@IDENTITY	
   SELECT BATCH_NUMBER = ??		
END

Open in new window

Rob RudloffIT Development SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
You do not need either a INSERT and/or an UPDATE statement all you need is a computed column.  

AS YourIdentityColumn % 1000

The only problem with this approach is that it will go from 999 to 0 (not 1).
0
Rob RudloffIT Development SpecialistAuthor Commented:
I do need to insert other info, in this case, but that is an interesting solution, I will see if the others here will accept using the Identitiy column % 1000.   I would still need to figure out how to deal with rolling over to 0, since I my requirements won't allow a 0 in that column.

I might have to do a select in the Insert procedure, before doing the actual insert itself.  Not sure of the syntax, but maybe something like:

CREATE TABLE [dbo].[MTRANS]
   ([MTRANS_ID] int IDENTITY (1, 1) NOT NULL,
    [MBatch]     int  NULL,
    [MStatus]    char(1) NULL
   )


CREATE PROCEDURE [prMTRANS_INSERT]
   (@MTRANS_ID  int OUTPUT,
    @MStatus    char(1),
    @NewBatch  int OUTPUT
   )
   	
As

BEGIN
   Begin
      SET ROWCOUNT 1				
      SET @LastMBatch = SELECT [MBatch] from [MTRANS] order by [MTRANS_ID] desc
      SET ROWCOUNT 0

      if  @LastMBatch >= 999
         begin
            set @LastMBatch = 1
         end    
      else
         set @NewBatch= @LastMBatch + 1

      INSERT INTO [MTRANS]([MStatus], [MBatch)
      VALUES (@mstatus,@NewBatch)		 
   End
	
   SELECT @@IDENTITY as TRANS_ID, @mbatch as BATCHNO
 
END

Open in new window

0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Try this. It is a calculation but gives you the rollover and persists the value.

-- Create the column as PERSISTED and use a calculation to set the
-- value of the column as 999 when it is directly divisible by 999
-- otherwise give you the remainder.
CREATE TABLE dbo.t1 (
           c1 int identity(1,1) Not null, 
           c2 as CASE WHEN (c1 % 999) = 0 THEN 999 ELSE c1 % 999 END persisted, 
           c3 varchar(5) not null
)

-- This is a test to see that you actually get the rollover after 999
INSERT INTO dbo.t1 (c3)
VALUSE ('test')
GO 2000

-- Check the values out after 998 to see that it rolls over.
SELECT *
FROM dbo.t1
WHERE c1 >= 999
ORDER BY  c1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob RudloffIT Development SpecialistAuthor Commented:
dbaduck -- Thanks.
I have not used "computed columns" before, but your example certainly does the trick.

What do you think of the example below, that someone suggested.  Basically, they fixed my "SET @LastMBatch = ..." line  to "SELECT TOP 1".
Do you think that one way is better than the other?
Thanks for your input.

CREATE PROCEDURE [prMTRANS_INSERT]
   (@MTRANS_ID  int OUTPUT,
    @MStatus    char(1),
    @MBatch     int OUTPUT
   )
   	
As

BEGIN
   Begin
      --SET ROWCOUNT 1				
      --SET @LastMBatch = SELECT [MBatch] from [MTRANS] order by [MTRANS_ID] desc
      --SET ROWCOUNT 0

      SELECT TOP 1 @mbatch = [MBatch] FROM [MTRANS] ORDER BY [MTRANS_ID] DESC
  
      if  @mbatch >= 999
         begin
            set @mbatch = 1
         end    
      else
          set @mbatch = @mbatch +1 

      INSERT INTO [MTRANS]([MStatus], [MBatch])
      VALUES (@mstatus,@mbatch)		 
   End
	
   SET @MTRANS_ID = SCOPE_IDENTITY()
   -- SELECT @@IDENTITY as TRANS_ID, @mbatch as BATCHNO
 
END

Open in new window

0
DBAduck - Ben MillerPrincipal ConsultantCommented:
You should use the TOP 1 or use MAX(MTRANS_ID). Personally I would use MAX if you want to know the max value in this column.  By doing a TOP 1 and Ordering, you incur a cost much greater than the MAX.

I would not use SET ROWCOUNT 1 at all because you need to do another SET ROWCOUNT 0 afterwards, which could be forgotten and affect everything else after that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.