?
Solved

Insert Values from a ParseList Function

Posted on 2009-04-26
2
Medium Priority
?
166 Views
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.

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
Comment
Question by:davidcahan
2 Comments
 
LVL 12

Accepted Solution

by:
expertsoul earned 2000 total points
ID: 24238005
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
 

Author Closing Comment

by:davidcahan
ID: 31574754
worked like a charm
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 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