How to generate a new code

Question

Using VB.Net and SQL Server

Table1

Id             Value .....

1001P0010001   100
1001P0010002   200
1001P0010003   300
1001P0010004   400
...

I have n columns and rows in table1, from the table1, I want to copy all the column details with new id no...

id no is like this 1001P0020001, 1001P0020002, .......

P002 is next id, P003 is the next Id.....

The first 4 digits and last 4 digits will remain as read from table1, middle 4 digits should change to next series

Expected output

Id             Value .....

1001P0010001   100
1001P0010002   200
1001P0010003   300
1001P0010004   400
1001P0020001   100
1001P0020002   200
1001P0020003   300
1001P0020004   400
...

Which the best way to do this?

I can do it in VB.Net or a SQL query...? Please suggest ways of doing this.

Answer 1

CREATE TABLE CocoJambo (
    Id  CHAR(12) NOT NULL,
    Value INT NULL,
    CHECK( Id LIKE '[0-9][0-9][0-9][0-9][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
);
GO

CREATE UNIQUE INDEX IUN_CocoJambo_Id
ON CocoJambo (Id);
GO

INSERT  CocoJambo (Id, Value)
          SELECT '1001P0010001', 100
UNION ALL SELECT '1001P0010002', 200
UNION ALL SELECT '1001P0010003', 300
UNION ALL SELECT '1001P0010004', 400
UNION ALL SELECT '1001P0020001', 100
UNION ALL SELECT '1001P0020002', 200
UNION ALL SELECT '1001P0020003', 300
UNION ALL SELECT '1001P0020004', 400;
GO

-- Test 1: generating a single Id
DECLARE @Prefix CHAR(5),
        @Sufix CHAR(4);
SELECT  @Prefix = '1001P',
        @Sufix = '0001';

BEGIN TRAN

DECLARE @LastGeneratedMiddleValue  INT,
        @LastValue INT;

SELECT  @LastGeneratedMiddleValue = y.MiddleValue,
        @LastValue = y.Value
FROM 
    (
    SELECT  x.MiddleValue, x.Value,
            ROW_NUMBER() OVER(ORDER BY x.MiddleValue DESC) AS RowNum
    FROM 
        (
        SELECT  CONVERT(INT,SUBSTRING(a.Id,6,3)) AS MiddleValue, a.Value
        FROM    CocoJambo a WITH(UPDLOCK) -- It will lock the rows (U lock) during transaction
        WHERE   a.Id LIKE @Prefix+'%'+@Sufix
        ) x
    ) y
WHERE   y.RowNum=1;

SELECT  @LastGeneratedMiddleValue  = ISNULL(@LastGeneratedMiddleValue ,0)
SELECT  @Prefix
        +RIGHT('00'+CONVERT(VARCHAR(3),@LastGeneratedMiddleValue +1),3)
        +@Sufix AS MyNewId,
        @LastValue AS Value

COMMIT TRAN;
GO

-- Test 2: generating many Id's
BEGIN TRAN

DECLARE @Results TABLE (
    Prefix CHAR(5) NOT NULL,
    Sufix CHAR(4) NOT NULL,
    LastGeneratedMiddleValue  INT NOT NULL,
    LastValue INT NULL
);
INSERT  @Results (Prefix, Sufix, LastGeneratedMiddleValue, LastValue)
SELECT  y.Prefix, y.Sufix, y.MiddleValue, y.Value
FROM 
    (
    SELECT  x.Prefix, x.MiddleValue, x.Sufix, x.Value,
            ROW_NUMBER() OVER(PARTITION BY x.Prefix, x.Sufix ORDER BY x.MiddleValue DESC) AS RowNum
    FROM 
        (
        SELECT  SUBSTRING(a.Id,1,5) AS Prefix,
                CONVERT(INT,SUBSTRING(a.Id,6,3)) AS MiddleValue,
                SUBSTRING(a.Id,9,4) AS Sufix,
                a.Value
        FROM    CocoJambo a WITH(UPDLOCK) -- It will lock the rows (U lock) during transaction
        ) x
    ) y
WHERE   y.RowNum=1;

SELECT  r.*, 
        r.Prefix
        +RIGHT('00'+CONVERT(VARCHAR(3),r.LastGeneratedMiddleValue +1),3)
        +r.Sufix AS MyNewId,
        r.LastValue AS Value
FROM    @Results r;

COMMIT TRAN;
GO