Splitstring/insert question

[wbodger]

I have some data that looks like this:

cola	colb
3360	182, 183, 739, 188
3361	2807, 2814, 2851, 2827, 1100, 188, 1431
3362	182, 183, 739, 188
3363	674, 1092, 1093, 1102, 1431
3364	182, 2811, 188
3365	182, 183, 739, 188
3366	182, 2811, 2817, 2829, 188, 1431
3367	182, 2812, 2818, 2830, 188, 1431

Which I need to insert into another table like:

cola	colb
3360	182
3360    183
3360    739
3360    188

I have a splitstring function, but I am missing something in my brain. What I need to do is

insert  into tblx(cola,colb,sId,cdate) 
select cola,colb,3042, GETDATE() from tbly where colb is not null

Where colb is a comma-delimited list of values that needs to be broken into separate rows as shown in the data above. Shoot, I can't even explain it well, no wonder I can't get the code down for it. Any thoughts?

wb

 

[wbodger]

And we are running 2008 r2

 

[wbodger]

I got it to work thru a couple of functions and table variables. Could probably be done better

ALTER  function [dbo].[fnSplitPVs]()
returns @tbl table ( cde_id int, pv int )
as
BEGIN

declare @info table(
id int identity(1,1),
cdeid int,
pvs varchar(100)
)

insert @info (cdeid,pvs) 
select cde_id, cListPVIDsforDEExport
from dbDEMapping.dbo.cdeimport_sck
where cListPVIDsforDEExport is not null

declare @splitTable table(
id int,
pv varchar(100)
)

declare @currentID int
declare @pvs varchar(100)

set @currentID = (select min(id) from @info)

while @currentID is not null
begin
    set @pvs = (select pvs from @info where id = @currentId)
    insert into @splitTable (id, pv) 
    select id, value from dbo.Split(@pvs,@currentID,',')
    set @currentID = (select min(id) from @info where id > @currentID)
end 
insert into @tbl (cde_id, pv)
select cdeid, pv from @info orig inner join @splitTable split on orig.id = split.id
RETURN
END

wb

 

[ralphtrent]

can you something like this?

select 'insert into new table values ('+ cola +','+ replace (colb,',','',') +')' from table

I am unable to test this out bt see if that works. This will create insert statements for each of your rows.

 

[wbodger]

So, are you thinking this instead of the function (well, two functions really, since one calls another)?

wb

 

[ralphtrent]

well that depends on how often you need to do this, if its a one time deal, just go function-less to create the insert statements and then run the insert statements, if you need to do this again and again then create a function.

Also, I was able to test my code above, it should look like this :

select 'insert into table values ('' + cola + '','' + replace(colb,',','','') + '')' from table