Insert Values from a ParseList Function

I have the following in my import staging table

ResortID int
ExperienceID  varchar(Max)

ExperienceID is a space delimited list of ID's.   I need to Parse the List of ExperienceID's and then insert each one into my ResortsToExperiences table with the coresponding ResortID.  I'm using a simple parselist function to return a table where each Row is one of the ExperienceID's in the space delimited string

I've been trying the following:


Insert into ResortsToExperiences (ResortID, ExperienceID)
Select a.ResortID,(Select str from dbo.ParseList(b.ExperienceID, ' '))
from #Temp a join #Temp2 b on a.ResortID = b.ResortID

This obviously doesn't work becaused the (Select str from ........) returns multiple values.  I get an error that "Subquery returned more than 1 value".  My head is melting trying to figure out how to do this.  I know there has to be a way.  I'll have at most about 50 unique Resorts in my Import table so i'm not opposed to using a cursor if i have to.  I'm attaching the parselist function just to make sure everyone knows what i'm referring to.

Thanks

USE [Infinity]
GO
/****** Object:  UserDefinedFunction [dbo].[ParseList]    Script Date: 04/26/2009 18:33:58 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ParseList]
(@list      varchar(4000),
                   @delimiter char(1) = ',')
       RETURNS @tbl TABLE (str int) AS
 
   BEGIN
      DECLARE @pos int
      DECLARE @tmpval varchar(4000)
      
      SET @list = LTRIM(RTRIM(@list))            
     IF RIGHT(@list,1) = @delimiter
          BEGIN
               SET @list = LEFT(@list, Len(@list) - 1)
          END
      SET @pos = charindex(@delimiter, @list)
 
      WHILE @pos > 0
      BEGIN
         SET @tmpval = left(@list, charindex(@delimiter, @list) - 1)
      INSERT @tbl (str) VALUES(@tmpval)
      SET @list = substring(@list, @pos + 1, len(@list))
         SET @pos = charindex(@delimiter, @list)
      END
 
      INSERT @tbl (str) VALUES(@list)
   RETURN
 
END

Select allOpen in new window