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 |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2013-04-19 : 09:21:14
|
| Hi allI need to insert a comma delimited list into a temp table with one record per element.So, data like this:-'1664339','1602531','1604891','1604743','3692983','5702028','1605330','1605431','1605225','4695246','1601455'Would need to go into a temp table as separate records.I currently have around 1500 items in my current list so loopin through will take a while and is probably not the best solution.I know I've seen some code that will do this, but I can't seem to find it using Google (probably using the wrong search terms).Would someone point me in the right direction please? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 09:35:52
|
| [code]INSERT INTO #TempTable(val)SELECT p.q.value('.', 'varchar(100)') FROM (SELECT CAST('<Values><Value>' + REPLACE( @CSVParam , ',', '</Value><Value>') + '</Value></Values>' AS xml) AS m)t CROSS APPLY m.nodes('/Values/Value')p(q)[/code]--Chandu |
 |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-04-19 : 09:37:28
|
| Thanks bandiWould I be able to use the results as part of an IN statement in a WHERE clause or am I attaching this the wrong way?What I need to do is reduce the length of the where clause to make editing easier. |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 09:40:58
|
| Yes you can use in WHERE clauseDECLARE @CSVParam VARCHAR(4000) = 'Your CSV Data'..WHERE YourColumn IN ( Above XML solution i.e. SELECT .....)--Chandu |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-04-19 : 10:34:42
|
| Thanks for that, I'll see what I can do with it. |
|
|
|
|
|
|
|
|
Adding delimited lists to a temp table