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

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

janetb
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:

http://www.sommarskog.se/arrays-in-sql-2008.html and
http://michaeljswart.com/2008/06/splitting-and-joining-strings-in-sql-server/

and I'm confused. Thx.

MuMu88
Aged Yak Warrior

547 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

janetb
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

3719 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: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000