Check this:
sql server - How to autoincrement a varchar - Stack Overflow[
^]
[EDIT]
A table definition should look like (replace
YourTableName
with the name you want to use):
CREATE TABLE YourTableName
(
ID INT IDENTITY(1,1),
CurrDate DATE,
CID INT,
VCID VARCHAR(10)
)
Let's insert sample data:
INSERT INTO YourTableName(CurrDate, CID, VCID)
VALUES('2016-08-23', 5, '2308160005'),
('2016-08-23', 6, '2308160006'),
('2016-08-23', 7, '2308160007')
Then you have to
create stored procedure[
^]
CREATE STORED PROCEDURE cspInsertMyData
AS
BEGIN
DECLARE @d DATE = GETDATE()
DECLARE @nextcid INT = 0
SELECT @nextcid = COALESCE(MAX(CID), 0) + 1 FROM YourTableName
INSERT INTO YourTableName (CurrDate, CID, VCID)
VALUES(@d, @nextcid, FORMAT(@d, 'ddMMyy') + RIGHT('0000' + CONVERT(VARCHAR(4), @nextcid), 4))
END;
A result:
ID CurrDate CID VCID
1 2016-08-23 5 2308160005
2 2016-08-23 6 2308160006
3 2016-08-23 7 2308160007
4 2016-08-23 8 2308160008
[/EDIT]