Sql syntax to insert into a sequece of row id's

[747576]

Hi there,

The code below creates a table of data called ORIGTABLE, this table has a id column called ORIGID, that increments by 1, for example 'ID1_001','ID1_002 and so on.
I have another table called TESTTABLE, which contains an id column called TESTID.

The idea is that the user selects a frequency value for varialble @CHECK_Seq, for example 15.
This means the 15th id in the sequence should be a TESTID and should be populated in the TESTTABLE.
The ID sequence would then just carry on in the ORIGTABLE:

If you run the script you will see what I mean.

I was wanting some help with the sql syntax to alter the sequencing slightly, so the TESTID skips 1 id in the sequence before being populated in the TESTTABLE, for example:

Select CHECK_Seq = 15
So:
ID1_014 = ORIGTABLE
ID1_015 = ORIGTABLE
ID1_016 = TESTTABLE (ORIGID still ID1_014 not ID1_015)

so the TESTTABLE would look like this:
TESTID DUPLICATETYPE ORIGID
ID1_016 TEST ID1_014

I hope this makes sense.

Thank you

DECLARE @ORIGTABLE TABLE
(
ORIGID varchar(30)
,IDNAME varchar(20)
 
)
 
DECLARE @ORIGTABLE_FINAL TABLE
(
ORIGID varchar(30)
,IDNAME varchar(20)
 
)
 
DECLARE @TESTTABLE TABLE
(
TESTID varchar(30)
,DUPLICATETYPE varchar(20)
,ORIGID varchar(20)
)
 
 
DECLARE         @CHECK_Counter int,
                        @CHECK_Seq int
                        
SET @CHECK_Seq = 15
 
 
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_001','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_002','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_003','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_004','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_005','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_006','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_007','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_008','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_009','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_010','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_011','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_012','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_013','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_014','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_015','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_016','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_017','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_018','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_019','ID1')
INSERT @ORIGTABLE(ORIGID,IDNAME)
VALUES('ID1_020','ID1')
 
DECLARE
@IDNAME           VARCHAR(20)
 
DECLARE         @ID int,
                        @NewIDCounter int,
                        @CHECKCounter int
 
SET @NewIDCounter = 1
SET @CHECK_Counter = 1
SET @IDNAME = 'ID1'
 
DECLARE CHECK_csr CURSOR FOR
 
SELECT CAST(RIGHT(ORIGID, 3) AS INT) FROM @ORIGTABLE
 
OPEN CHECK_csr
 
FETCH NEXT FROM CHECK_csr INTO @ID
 
WHILE @@FETCH_STATUS = 0
BEGIN
                
 
                IF (@CHECK_Counter=@CHECK_Seq) AND (@NewIDCounter > 1)
                        BEGIN
                        --PRINT 'Insert CHECK here'
                                                --Insert a Duplicate
INSERT INTO @TESTTABLE (TESTID,DUPLICATETYPE,ORIGID)
VALUES
(@IDNAME+ '_' + REPLICATE('0', ABS(LEN(CAST(@NewIDCounter AS VARCHAR))-3)) + CAST(@NewIDCounter AS VARCHAR)
,'TEST'
,@IDNAME+ '_' + REPLICATE('0', ABS(LEN(CAST(@NewIDCounter-1 AS VARCHAR))-3)) + CAST(@NewIDCounter - 1 AS VARCHAR))
SET @NewIDCounter = @NewIDCounter + 1
                
                                SET @CHECK_Counter = 1
 
                        END
                
 
                                INSERT INTO @ORIGTABLE_FINAL
                                (ORIGID,IDNAME)
                                SELECT @IDNAME+ '_' + REPLICATE('0', ABS(LEN(CAST(@NewIDCounter AS VARCHAR))-3))
                                + CAST(@NewIDCounter AS VARCHAR),IDNAME
                                FROM @ORIGTABLE WHERE CAST(RIGHT(ORIGID, 3) AS INT) = @ID
                                
                                SET @NewIDCounter = @NewIDCounter + 1
                                SET @CHECK_Counter = @CHECK_Counter + 1
 
        FETCH NEXT FROM CHECK_csr INTO @ID
END
 
CLOSE CHECK_csr
DEALLOCATE CHECK_csr
 
SELECT * FROM @TESTTABLE
SELECT * FROM @ORIGTABLE_FINAL