Insert into SQL next in sequence

Question

I have a table in SQL with a column called Credit Card Code

Data eg

CCARD-000093
CCARD-000094
CCARD-000095
CCARD-000096
CCARD-000097
CCARD-000098
CCARD-000099
CCARD-000100
CCARD-000101

When inserting data into this table, how can I get the next value in the sequence?

This is what I have so far

SELECT 'CCARD-' + 
(SELECT CAST(MAX(CAST(SUBSTRING(ccc.CreditCardCode,7, 6) as INT)) AS 
NVARCHAR(MAX)) FROM CustomerCreditCard ccc)

This gives CCARD-101

How can I get this to return CCARD-000101 ???

Please share sequence code and what next value for returns. — artm
– artm, Commented Jul 6, 2016 at 7:33
I couldn't get the syntax for NEXT VALUE FOR correct, so I couldn't get it to return a value. — Brendan Gooden
– Brendan Gooden, Commented Jul 6, 2016 at 7:33
You better use a computed column for this data and just use a regular int identity. This way you don't have to worry about calculating the next value. — Zohar Peled
– Zohar Peled, Commented Jul 6, 2016 at 7:41

mohan111 · Accepted Answer · 2016-07-06 07:46:31Z

1

if you want to increment the table while inserting Record you can auto increment using Identity and Presisted Concept

IF OBJECT_ID('TestTables', 'U') IS NOT NULL 
BEGIN
    DROP TABLE TestTables 
END

Reset the identity from where you want to start

DBCC checkident ('TestTables', reseed, 93)

CREATE TABLE TestTables
(
    CreditCard AS ('CCARD-0000' + CAST(AutoIncId as varchar)) PERSISTED NOT NULL PRIMARY KEY,    
    [AutoIncId] INT IDENTITY NOT NULL,
    [Bank] VARCHAR(10)
);


INSERT INTO TestTables ([Bank]) VALUES ('Indus')
INSERT INTO TestTables ([Bank]) VALUES ('ICICI')
INSERT INTO TestTables ([Bank]) VALUES ('HDFC')
INSERT INTO TestTables ([Bank]) VALUES ('DBS')

SELECT * FROM TestTables

answered Jul 6, 2016 at 7:46

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brendan Gooden Over a year ago

Can you please explain your answer in the context of my question? If I was to insert into the column CreditCardCode how would I increment that value, based off the highest current value?

mohan111 Over a year ago

if the data is inserted directly into the table then you can follow this process if you want to insert on maximum value then you need to go through MAX select @BrendanGooden i will post you

Serg · Accepted Answer · 2016-07-06 08:55:56Z

1

Next value, fixed length, with leading '0':

SELECT 'CCARD-' + RIGHT(REPLICATE('0',6)+CAST(1+MAX(CAST(SUBSTRING(ccc.CreditCardCode,7, 6) as INT)) AS NVARCHAR(6)),6) 
FROM CustomerCreditCard ccc

answered Jul 6, 2016 at 8:55

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Comments

Brendan Gooden · Accepted Answer · 2016-07-06 08:59:56Z

This seemed to do the trick

INSERT INTO CustomerCreditCard (CustomerCode, CreditCardCode, CreditCardDescription, NameOnCard, Address, City, State, PostalCode, Country, ExpMonth, ExpYear, Telephone, Email, IsActive,  ResidenceType, UserCreated, DateCreated, UserModified, DateModified, County, StartMonth, StartYear)
  SELECT c.CustomerCode, 
    (SELECT 'CCARD-' + REPLICATE('0', 6 - LEN((SELECT CAST(MAX(CAST(SUBSTRING(ccc.CreditCardCode,7, 6) as INT)) + 1 AS NVARCHAR(MAX)) FROM CustomerCreditCard ccc))) +
(SELECT CAST(MAX(CAST(SUBSTRING(ccc.CreditCardCode,7, 6) as INT)) + 1 AS NVARCHAR(MAX)) FROM CustomerCreditCard ccc)), 
    'Web Credit Card',
    c.CustomerName, 
    c.Address, 
    c.City, 
    c.State, 
    c.PostalCode, 
    c.Country, 
    'Jan', 
    2025, 
    c.Telephone, 
    c.Email, 
    1, 
    c.ResidenceType, 
    'webadmin', 
    GETDATE(), 
    'webadmin', 
    GETDATE(), 
    c.County, 
    'Jan',
    2010
    FROM Customer c
  WHERE c.CustomerCode = 'CUST-015056'

Collectives™ on Stack Overflow

Insert into SQL next in sequence

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related