FLetz assume the key column (BCA11000000001) name as DataKey. Letz create the table as:
CREATE TABLE dbo.MyTable
(
DataKey int NOT NULL PRIMARY KEY,
OtherKey varchar(50),
....
)
SQL maintains DataKey column as identity primary column, starting from 1. Expected key is constructed using SELECT command as:
SELECT 'BCA'+ substring(year(getdate()),1,2) + Cast(Datakey as varchar(10)
It will result the expected code BCA11000000001