Insert Values from a ParseList Function

Posted on 2009-04-26
Last Modified: 2012-05-06
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.

USE [Infinity]


/****** Object:  UserDefinedFunction [dbo].[ParseList]    Script Date: 04/26/2009 18:33:58 ******/





ALTER FUNCTION [dbo].[ParseList]

(@list      varchar(4000),

                   @delimiter char(1) = ',')

       RETURNS @tbl TABLE (str int) AS


      DECLARE @pos int

      DECLARE @tmpval varchar(4000)


      SET @list = LTRIM(RTRIM(@list))            

     IF RIGHT(@list,1) = @delimiter


               SET @list = LEFT(@list, Len(@list) - 1)


      SET @pos = charindex(@delimiter, @list)

      WHILE @pos > 0


         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)


      INSERT @tbl (str) VALUES(@list)



Open in new window

Question by:davidcahan
    LVL 12

    Accepted Solution

    What you need here is CROSS APPLY.

    Try this link:

    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


    Author Closing Comment

    worked like a charm

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now