select left(patientName,3)+repalce(dob,'-','')+'01' from table1

'01' - what if you have more then 99 rows?

how do you link this two table?

i think you must have some primary key on first table and set it as foreign key in table2.

for example

create table table1 (idPN int, patientName varchar(150), DOB varchar(20), Age int, Sex varchar(1)

constraint pk_table1_idPN PRIMARY KEY(idPN))

create table table2(idPN int, regno varchar(50)

FOREIGN KEY (idPN) REFERENCES table1(idPN))

think about that