insert comma seperated values in table

[Former Member]

@id = 1,2,3,4,5
@name = 'NY, NJ, VA, DC,CA'

I have a temp table called #tmp (id int, name varchar(4)). I want to insert these above comma delimited values in this temp table, so 1 has corresponding value = NY, 2 has NJ etc...so total 5 rows will be inserted. What is the best way to code that in SQL Anywhere?

[graeme_perrow]

You want to use the sa_split_list function to break the strings into a result set. Since there are two of them, you'll need to join them. This seems to work:

insert into #tmp (id,name) select ID.row_value id, VAL.row_value name 
    from sa_split_list(@name) VAL join sa_split_list(@id) ID on ID.line_num=VAL.line_num

[Former Member]

Thats perfect. Thanks a lot all.