SQL Server split comma into table variable

Question

I am using SQL Server 2014 and I have the following code:

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(39572),(39793);   

I want to do something like this to do the same result:

declare @listOfIDs table (id int);
insert @listOfIDs(id) split('39572,39793');   

Any ideas?

Answer 1

If your split function is a table function, then you should have your insert statement as follows.

Insert @listofIDs(id)
Select * From split('39572,39793');

Answer 2

There are many Split/Parsing functions out there.

Here is one

declare @listOfIDs table (id int);
insert @listOfIDs(id) Select Key_Value from [dbo].[udf-Str-Parse]('39572,39793',',')

Select * from @listOfIDs

Returns

id
39572
39793

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Edit

FYI if you just run

Select * from [dbo].[udf-Str-Parse]('39572,39793',',')

It returns

Key_PS  Key_Value
1       39572
2       39793

I've found the Sequence Number (Key_PS) to be valuable at times.