split function in sql server
How to split column and insert other table
my db data this type 2 table
create table tbl_Employee
(
EmpID int,
Allowance nvarchar(500)
)
input data this type
1 Basic:1000,TA:500,DA:400,
2 Basic:1000,TA:500,DA:400,PA:500,CA:200,
3 Basic:1000,TA:500,DA:400,PF:150,
create table tbl_salary
(
EmpID int,
Type varchar(100),
Amount money
)
columns are string data type and I need to convert output data this type
EmpID Type Amount
1 Basic 1000.00
1 TA 500.00
1 DA 400.00
2 Basic 1000.00
2 TA 500.00
2 DA 400.00
2 PA 500.00
2 CA 200.00
3 Basic 1000.00
3 TA 500.00
3 DA 400.00
3 PF 150.00