a slightly more complicated csv insert 1

[bouwob]

So I am using a function that was built based on this thread
thread732-1352819

in my newest SP I am getting a data field that looks similer to this

"1,56786789|2,787667976|3,658765666"

every pipe is a row, every comma is a column.

I have declared this tables in my SP


declare @tempdat varchar(800)
set @tempdat = '1,56786789|2,787667976|3,658765666'

DECLARE @rows TABLE
(
num varchar(25),
Ident smallint
)

and have so far progressed with this sql statement

insert into @rows (ccnum, ccIdent) select * from Split(select [Value] from Split(@tempdat, '|'), ',')

which isnt even close.

Am I going to be forced to create 2 temp tables to hold the information, or is there a slick statement that will take care of it all at once.

 

[gmmastros]

You cannot split a split.

Split returns a table valued function. You cannot use a table as a parameter to a function.

If your data is always nice, and you only have 2 pieces of data per row, then you can get a little creative to achieve your results.

I was able to get your code to run properly on my server after making several changes.

declare @tempdat varchar(800)
set @tempdat = '1,56786789|2,787667976|3,658765666'

DECLARE @rows TABLE
(
  num varchar(25),
  Ident [!]int[/!]
)

insert into @rows ([!]num[/!], [!]Ident[/!]) 
select Parsename(Replace([Value], ',', '.'), 2),
       Parsename(Replace([Value], ',', '.'), 1)
from   dbo.Split(@tempdat, '|')

If your data is more complicated than this, then you will need to use multiple table variables or more code with a single table variable. I am using the ParseName function here. ParseName can only accomodate 4 pieces of data. If you have more than that, you will need to find another solution.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bouwob]

That works excellent. Thanks for the help. Wasnt aware of Parsename.


You just got to love this forum. To many smart people for my own good