insert comma seperated values in table

@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?

insert list

asked 12 Feb '13, 19:09

roadtrain64
136●6●7●10
accept rate: 0%

edited 12 Feb '13, 20:33

Graeme Perrow
9.6k●3●79●124

flat view

3 Answers:

active answers oldest answers newest answers popular answers

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

permanent link

answered 12 Feb '13, 20:32

Graeme Perrow
9.6k●3●79●124
accept rate: 54%

edited 12 Feb '13, 20:32

Thats perfect. Thanks a lot all.

(13 Feb '13, 17:12) roadtrain64

flat view

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;

permanent link

answered 12 Feb '13, 20:38

Mark Culp
24.9k●10●141●297
accept rate: 41%

edited 13 Feb '13, 08:48

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

(12 Feb '13, 20:40) Mark Culp

flat view

CREATE TABLE #tmp_tbl
          ( qualifier_state_id    integer
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          )
INSERT INTO #tmp_tbl(qualifier_state_name) VALUES ('SAMLL')
INSERT INTO #tmp_tbl(qualifier_state_name) VALUES ('YELLO')

Thanks but I have qualifier_state_name in my temp table and my qualifier_state_ids are coming from variavle @qualifier_state_ids = '58,61' Where 58= SMALL and 61= Yello

Now I need to update below temp table and make qualifier_state_id =58 for small and 61 for Yello I tried below but not workin

UPDATE #tmp_tbl                             a
                    , sa_split_list(@lQualifierStateIds)   b
                  SET a.qualifier_state_id   = TRIM(b.row_value)-- this updates both state_ids =61 whichh is wrong.

permanent link

answered 14 Feb '13, 16:52

roadtrain64
136●6●7●10
accept rate: 0%

edited 14 Feb '13, 16:56

Breck Carter
32.5k●541●726●1050

Okay Thanks a lot.

(16 Feb '13, 11:14) roadtrain64

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

insert ×34
list ×12

question asked: 12 Feb '13, 19:09

question was seen: 4,167 times

last updated: 16 Feb '13, 14:07

SAP SQL Anywhere

insert comma seperated values in table

Follow this question

Related questions