• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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

Open in new window

0
davidcahan
Asked:
davidcahan
1 Solution
 
expertsoulCommented:
What you need here is CROSS APPLY.

Try this link: http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Something similar should work:




Insert into ResortsToExperiences (ResortID, ExperienceID)
SELECT a.ResortID, PL.str
FROM #Temp a 
INNER JOIN #Temp2 b on a.ResortID = b.ResortID 
CROSS APPLY dbo.ParseList(b.ExperienceID, ' ') as PL

Open in new window

0
 
davidcahanAuthor Commented:
worked like a charm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now