| Author |
 Topic  |
|
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 05/14/2013 : 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 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
549 Posts |
Posted - 05/14/2013 : 15:01:53
|
| can you provide some example data, for table1 and table2... |
 |
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 05/14/2013 : 16:26:04
|
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
|
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 05/14/2013 : 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/ |
|
|
| |
Topic |
|
|
|
Comma delimited list for join update