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
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.
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,
@MBatch int OUTPUT
INSERT INTO [CCARD_BATCH]([MStatus])
-- do magic here to increment/rollover MBatch ?
SELECT TRANS_ID = @@IDENTITY
SELECT BATCH_NUMBER = ??