--
First Create The Tables :
Generate Student Ids :
create table CSE
(
Id int identity(1,1),
Pid as case len(Id) when 1 then 'CSE'+'000'+convert(varchar,ID)
when 2 then 'CSE'+'000'+convert(varchar,ID)
else 'CSE'+convert(varchar,ID)
end
)
--insert into CSE default values
create table ECE
(
Id int identity(1,1),
Pid as case len(Id) when 1 then 'ECE'+'000'+convert(varchar,ID)
when 2 then 'ECE'+'000'+convert(varchar,ID)
else 'ECE'+convert(varchar,ID)
end
)
--insert into ECE default values
create table EEE
(
Id int identity(1,1),
Pid as case len(Id) when 1 then 'EEE'+'000'+convert(varchar,ID)
when 2 then 'EEE'+'000'+convert(varchar,ID)
else 'EEE'+convert(varchar,ID)
end
)
--insert into EEE default values
create table IT
(
Id int identity(1,1),
--BranchName varchar(10),
Pid as case len(Id) when 1 then 'IT'+'000'+convert(varchar,ID)
when 2 then 'IT'+'000'+convert(varchar,ID)
else 'IT'+convert(varchar,ID)
end
)
--insert into IT values('IT')
create table MECH
(
Id int identity(1,1),
Pid as case len(Id) when 1 then 'MECH'+'000'+convert(varchar,ID)
when 2 then 'MECH'+'000'+convert(varchar,ID)
else 'MECH'+convert(varchar,ID)
end
)
--insert into MECH default values
create table CIVIL
(
Id int identity(1,1),
Pid as case len(Id) when 1 then 'CIVIL'+'000'+convert(varchar,ID)
when 2 then 'CIVIL'+'000'+convert(varchar,ID)
else 'CIVIL'+convert(varchar,ID)
end
)
--insert into CIVIL default values
create table PROD
(
Id int identity(1,1),
Pid as case len(Id) when 1 then 'PROD'+'000'+convert(varchar,ID)
when 2 then 'PROD'+'000'+convert(varchar,ID)
else 'PROD'+convert(varchar,ID)
end
)
create table Temp123(StudentId varchar(10))
------------------------------------------------------------------