Solved

SQL Insert with Identity value AND incremental rollover column ?

Posted on 2014-02-25
5
836 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
[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
  • 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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

626 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