Insert column value based on auto increment column

Question

I have a table in my SQL Server database with the following columns

ID |           NAME    |              DOB      |          APPLICATION NO |
1  |          JOHN     |           31/05/1986  |            KPL\2014\1   |
2  |          MARY     |           26/04/1965  |            KPL\2014\2   |
3  |         VARUN     |           15/03/1972  |            KPL\2014\3   |

Here, the column IDis an auto-increment column, and the column is APPLICATION NOdependent on ID.

This means what APPLICATION NOis concatenation KPL\, YEARand the value of the column ID.

Then how can I make an insert request?

Answer 1

Why aren't you using a calculated column?

I would change the definition of the table.

add year column

add the column "application_name" (if it is not always "KPL").

alter table <yourTable> add computed_application_name as (application_name + '/' + CAST(<yearColumn> as VARCHAR(4) + '/' + <otherColumn> + CAST(id as VARCHAR(MAX))

Answer 2

:

alter table t
    add application_no as ('KPL' + cast(year(getdate()) as varchar(255)) + cast(id as varchar(255));

, , ​​. createdat, getdate():

alter table t add CreatedAt datetime default getdate();

, ( , ):

update table t set CreatedAt = getdate();

application_no:

alter table t
    add application_no as ('KPL' + cast(year(CreatedAt) as varchar(255)) + cast(id as varchar(255));

Answer 3

SCOPE_IDENTITY

Declare @Id INT    
Insert Into TABLENAME(NAME ,DOB )
Values('Name','1/1/1990')

Set @Id=SCOPE_IDENTITY()

IF(@Id>0)
begin
    declare @col varchar(100)=''
    set @col= 'KPL\' + CONVERT(varchar(4),YEAR(GETDATE()))+'\'  + CONVERT(varchar(4),@Id)

    Update TABLENAME
    SET APPLICATIONNO= @col
    Where ID = @id
end

Answer 4

"APPLICATION_NO":

create table tbl (
    ID int, 
    NAME varchar(10),              
    DOB  date,          
    APPLICATION_NO as ('KPL\'+cast(year(dob) as char(4))+'\'+cast(id as varchar)) 
)

.

Answer 5

. application_no. .

- :

CREATE TRIGGER [TRIGGER_TABLE1] ON  TABLE1
    AFTER INSERT
AS 
BEGIN

    UPDATE TABLE1
    SET APPLICATION_NO = 'KPL\' + CONVERT(VARCHAR, YEAR(GETDATE())) + '\' + CONVERT(VARCHAR, ID)
    WHERE APPLICATION_NO IS NULL

END

EDIT: , . APPLICATION_NO NULL. , , .

Answer 6

insert:

• ,

• TABLE1 - , , TABLE2 - , :

  DECLARE @MAXID INT
  SELECT @MAXID = MAX(ID) FROM TABLE1
  
  INSERT INTO TABLE1
  (NAME,DOB,APPLICATION_NO)
  SELECT
  NAME,
  DOB,
  'KPL\' 
  + CONVERT(CHAR(4),YEAR(GETDATE()) +'\'
  + CONVERT(VARCHAR(25), @MAXID + ROW_NUMBER() OVER (ORDER BY NAME)) AS APPLICATION_NO
  FROM TABLE2