First of all thanks for your valuable comment with different approach.
R.P.Rozema, sql :
Actually in my scenario we can access database only by SP. So we must not use any inline query, so we cannot create a SELECT query using UNION ALL.
timothyawiseman, Scott Roberts:
As far as bulk insert is concern, I think that it is very efficient for very very large dataset. Here we are dealing with some 100 / 500 odd rows.
Galina Likhovetsky:
Actually our application is using JAVA as a language. Yes agree, if it would be at Microsoft platform, definitely I would have suggested and think about your approach.
Darrell Bauer:
The table-valued function [dbo].[TableFormDelimetedStringWithoutNumberList] will choke on lists longer than 100 elements. Here's the error:
Oops I missed it;-). Good catch.
;WITH CTETable (start, stop)
........
........
........
WHERE stop > 0
OPTION(MAXRECURSION 0)
you can add this hint to the sql in specified function.
Stephen Byrne:
I think it is not inserting one record at a time rather it is inserting in a set. So you will get a generic error like (data would be truncated, FK or unique constraints voilated etc.)
Lee Forst:
I haven't tested for it that much records, but our application is working fine dealing with upto 400 - 500 records in an XML.
Will test for more records and post the result.