Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Comma delimited list for join update
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

71 Posts

Posted - 05/14/2013 :  12:53:58  Show Profile  Reply with Quote
I have table1 query string that pulls the following from a remote table whereby myKeys is actually a comma-delimited list:
table1: myKeys varchar, myCount int, myComment varchar

I need to update a local 2008r2 table based on:
table2: split somehow - myKeys int, myCount int, myComment varchar

Currently, I have a SSIS package that pulls the information and creates a local table. I've seen some answers using functions to feed a select statement (e.g. myKeys), but nothing that includes multiple fields e.g. (myKeys, myCount, myComment).

I'm asking for guidance because I don't know where to start. I've looked at: and

and I'm confused. Thx.

Aged Yak Warrior

549 Posts

Posted - 05/14/2013 :  15:01:53  Show Profile  Reply with Quote
can you provide some example data, for table1 and table2...
Go to Top of Page

Yak Posting Veteran

71 Posts

Posted - 05/14/2013 :  16:26:04  Show Profile  Reply with Quote
I got this to work. Is it a good idea?

Setup: tableX (myID, myCount, myComments)
2,3 4 parking23
1 0 parking1
5 0 parking5

Then, to split/update, I've got this going on:

create table #temp (myID int, myCount int, myComments varchar(50));
with xmlData (xml_my_id,myCount,myComments) as (select cast('<x>'+replace(myXid,',','</x><x>')+'</x>' as xml) AS xml_my_id, myCount,myComments from dbo.tableX)

insert into #temp (myID, myCount, myComments)
select x.value('.','int') AS my_id, myCount, myComments from xmlData cross apply xmlData.xml_my_id.nodes('//x') as func(x);
update t1 set t1.myCount=#temp.myCount, t1.myComments=#temp.myComments from dbo.table1 t1 right join #temp on t1.myID=#temp.myID
drop table #temp
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 05/14/2013 :  16:54:54  Show Profile  Reply with Quote
It would work well, except watchout for couple of things:

1. This is a very inefficient way to parse the comma-separated values, perhaps the most inefficient that I have seen. So if you have large amounts of data, you would run into peformance issues.

2. If any of the fields you are parsing have any XML special characters (e.g. &, >, < etc.), this would fail.

For an fast string splitter that does not have the XML issues, take a look at Jeff Moden's article here, especially the function in Figure 21:
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000