Solved

SQL Insert with Identity value AND incremental rollover column ?

Posted on 2014-02-25
5
814 Views
Last Modified: 2014-03-05
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

0
Comment
Question by:Rob Rudloff
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39887690
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
 

Author Comment

by:Rob Rudloff
ID: 39888571
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
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 400 total points
ID: 39891929
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
 

Author Comment

by:Rob Rudloff
ID: 39892610
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
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 400 total points
ID: 39892772
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 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