Update unique value (String) for each insert when doing multiple insert-CodePudding

I am performing insert operation like follow on Microsoft SQL Server Management Studio:

INSERT INTO contact (scode, slastname, sfirstname)
    (SELECT     
         ******,
         t.slastname,
         t.sfirstname
     FROM   
         table1 t
     WHERE  
         t.slastname NOT IN (SELECT slastname FROM contact)
         AND t.slastname <> '' 
         AND t.istatus IN (0, 3, 4))

This insert is supposed to make few hundreds of insert.

But here I want to insert scode like C000512, C000513, C000514 ....

and C000511 being the latest scode entry previously present in contacts.

How do I make this SCODE insert unique for each insert?

Previously I have tried

(select substring((select max(scode) from contact), 0, 5)   '0'   
     cast(cast(substring((select max(scode) from contact),4,8) as
int)  1 as varchar))

or:

(SELECT Substring((SELECT Max(scode) FROM contact), 1, 5)
      CONVERT(VARCHAR, Substring((SELECT Max(scode) FROM contact), 4, 8)   1, 101)),

Or also by creating a variable. But SCODE wasn't updating.

Any suggestions how I can make this work?

CodePudding user response：

The best solution - I think - would be to use

an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.Contact
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    SCODE AS 'C'   RIGHT('000000'   CAST(ID AS VARCHAR(6)), 6) PERSISTED,
    sLastName VARCHAR(100),
    sFirstName VARCHAR(100)
    -- your other columns here
  )

Now, every time you insert a row into Contact without specifying values for ID or SCODE:

INSERT INTO dbo.Contact(sLastName, sFirstName, ......)
VALUES ('Doe', 'John', ......)

then SQL Server will automatically increase your ID value, and SCODE will contain values like C000512, C000513,...... and so on - automatically, safely, reliably, no duplicates.

CodePudding user response：

Put it into a transaction, row_number new rows.

begin tran;
with mx as(
   select cast(substring(max(scode), 2, 7) as 
int) n 
   from contact)
INSERT INTO contact (scode, slastname, sfirstname)
SELECT     
         'C'  right('000000'   cast(mx.n   row_number() over(order by (select null)) as varchar(6)), 6) ,
         t.slastname,
         t.sfirstname
     FROM   
         table1 t
         cross join mx
     WHERE  
         t.slastname NOT IN (SELECT slastname FROM contact)
         AND t.slastname <> '' 
         AND t.istatus IN (0, 3, 4);
  commit tran;