Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Insert with Identity value AND incremental rollover column ?

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 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