SP - Convert CSV to a Link table

[inspireuk]

I have a CSV file containing linked IDs eg 1,2,3

I have a table with two columns ID and LinkedID

I would like to pass the CSV string in and have the table updated so that each relationship is added as a new record

ID,LinkedID
1,2
1,3
2,1
2,3
3,1
3,2

Any ideas?

Thanks

Josh

[azimmer]

I have a CSV file containing linked IDs eg 1,2,3

I have a table with two columns ID and LinkedID

I would like to pass the CSV string in and have the table updated so that each relationship is added as a new record

ID,LinkedID
1,2
1,3
2,1
2,3
3,1
3,2

Any ideas?

Thanks

Josh

Why not import the CSV file into a table directly? (Enterprise Manager -> Tools -> Data Transformation Services -> Import Data ...)

[inspireuk]

because the list is dynamically generated.

Josh

[azimmer]

You mean that you need a procedure that takes a CSV string (of the from "ID,LinkedID") as an input parameter and then adds the the record to the table? (If so I was confused of your mentioning a "CSV file".)

The following procedure does just that:

create procedure csvinsert (@csvstring as varchar(200))
as
declare @commapos as int
 
set @commapos = charindex(',',@csvstring)
insert into IDTABLE VALUES(left(@csvstring,@commapos-1),right(@csvstring,len(@csvstring)-@commapos))
 

[inspireuk]

sorry shouldn't have used the phase 'csv file'. Let me try to explain again what i'm trying to do.

the sp would have a string input of '1,2,3'

from this i need to generate a list of all the possible combinations
1,2
1,3
2,1
2,3
3,1
3,2

which then need to be inserted into a two column table.

the string input could be any length eg '1,2,3,4,5' and so the generated list would be different.

i hope this makes sense.

thanks for your help

Josh

[azimmer]

sorry shouldn't have used the phase 'csv file'. Let me try to explain again what i'm trying to do.

the sp would have a string input of '1,2,3'

from this i need to generate a list of all the possible combinations
1,2
1,3
2,1
2,3
3,1
3,2

which then need to be inserted into a two column table.

the string input could be any length eg '1,2,3,4,5' and so the generated list would be different.

i hope this makes sense.

thanks for your help

Josh

Next attempt :):

create procedure create_linked_ids(@csvstring as varchar(200))
as
declare @pos as int, @lastpos as int
declare @temptbl TABLE (tmpid int)
 
set @lastpos = 1
set @pos = charindex(',',@csvstring)
while (@pos > 0)
begin
    insert into @temptbl values (substring(@csvstring,@lastpos,@pos-@lastpos))
    set @lastpos=@pos+1
    set @pos=charindex(',',@csvstring,@lastpos)
end
insert into @temptbl values (substring(@csvstring,@lastpos,len(@csvstring)-@lastpos+1))
select a.tmpid 'ID',b.tmpid 'LinkedID' from @temptbl a, @temptbl b where a.tmpid<>b.tmpid
 
go
 

Use it like this:

exec create_linked_ids '1,2,3'

If the result has to go into a table then insert the 'INTO <tablename>' caluse into the last line of the procedure (before 'FROM').

Hope it helps

[inspireuk]

that works great! thanks.