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?

[MarkCulp]

Try something like this:

insert into #tmp( id, name )
select a.row_value, b.row_value
  from sa_split_list( @id ) a
  join sa_split_list( @name ) b
    on a.line_num = b.line_num;

Note that I have not tested this. You can read more information about sa_split_list in the docs.

---

I noticed that you have some embedded spaces in your comma-delimited strings. If you don't want those included in your tmp table then you will can use trim to remove any that exists. Example:

insert into #tmp( id, name )
select trim( a.row_value ), trim( b.row_value )
  from sa_split_list( @id ) a
  join sa_split_list( @name ) b
    on a.line_num = b.line_num;

[MarkCulp]

Looks like Graeme hit enter a few minutes before I did 🙂 ... I took too long to look up the references in the docs!