Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2013-05-14 : 12:53:58
|
| 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 varcharI need to update a local 2008r2 table based on:table2: split somehow - myKeys int, myCount int, myComment varcharCurrently, 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 andhttp://michaeljswart.com/2008/06/splitting-and-joining-strings-in-sql-server/and I'm confused. Thx. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-14 : 15:01:53
|
| can you provide some example data, for table1 and table2... |
 |
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2013-05-14 : 16:26:04
|
| I got this to work. Is it a good idea?Setup: tableX (myID, myCount, myComments)2,3 4 parking231 0 parking15 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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-14 : 16:54:54
|
| 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/ |
|
|
|
|
|
|
|
|
Comma delimited list for join update