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
Loading
Pradeep ShetPosted Jul 29, 2014, 3:12 PM
Create a user defined function which will return comma seperated value in
different rows. Then using while loop insert it in database
CREATE FUNCTION dbo.Spliter ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(10) ) RETURNS TABLE AS RETURN ( SELECT Item = SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number) FROM tableName WHERE Number <= CONVERT(INT, LEN(@List)) AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter );
If this helped you to solve your problem, please mark it answered