Hello, I found some table valued function for splitting string in sp and getting values in columns, but when data is huge these functions are became slow. so if anybody has a good function for splitting values separated by commas into rows of a table... please provide me.
The link where I found some functions: Click Here
It takes a long time to process precisely BECAUSE there is a lot of data. You aren't going to escape that.
The problem is that you are storing multiple values in a single column. This violates First Normal Form. As a result, you cannot create indexes on these columns to search them more efficiently.
I suggest you improve your design to split the comma text into separate columns. You can copy your data to the new improved design using a query like the one you've already written.
I use Erland Sommarskog's chunk_split_me function. I doubt you will find any articles which go in to more depth regarding the relative performance merits of each approach to the problem.
If none of his functions work for you then you'll probably need to look to batch processing.