Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Insert with Identity value AND incremental rollover column ?

Posted on 2014-02-25
5
Medium Priority
?
869 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 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1600 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1600 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

576 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