SAP SQL Anywhere

Question

I want to insert in a table with a select from another.

    INSERT INTO tblcustomer (c_id, c_type, c_kont, c_var) 
    SELECT k_id, 1, 1, 4001
    FROM tblKont
    WHERE tblkont.k_id_nr Is Null;

The problem is that I want insert 6 equal rows apart from c_var have different values. The c_var values is: 4001, 4003, 4006, 4009, 4020, 4032 That is, for each row that has tblkont.k_id_nr zero to have six rows in tblCustomer

Let's say I have a c_id who is 15 Then it should look like this in the table tblCustomer.

---c_id---c_type---c_kont----c_var 
----------------------------------- 
--- 15 ----- 1 ----- 1 ----- 4001 
--- 15 ----- 1 ----- 1 ----- 4003 
--- 15 ----- 1 ----- 1 ----- 4006 
--- 15 ----- 1 ----- 1 ----- 4009 
--- 15 ----- 1 ----- 1 ----- 4020 
--- 15 ----- 1 ----- 1 ----- 4032

Accepted Answer

2

Here's another method without using a further table:

You can use the system procedure sa_split_list() to generate a result set that contains a row for each listed value:

SELECT * FROM sa_split_list('4001, 4003, 4006, 4009, 4020, 4032')

returns a result set with 6 rows:

line_num    row_value
1           4001 
2           4003
3           4006
4           4009 
5           4020  
6           4032

So if you use that instead of the separate table from Justin's sample, you will get an identical result via

INSERT INTO tblcustomer (c_id, c_type, c_kont, c_var) 
SELECT k_id, 1, 1, row_value
FROM tblKont CROSS JOIN sa_split_list('4001, 4003, 4006, 4009, 4020, 4032')
WHERE tblkont.k_id_nr IS NULL;

permanent link

answered 09 Oct '14, 03:05

Volker Barth
39.3k●354●538●808
accept rate: 33%

Amazing. Is it possible to have values for multiple columns with sa_split_list too?

(09 Oct '14, 03:22) Rolle

Replies hidden

Not directly.

You could however use several sa_split_list calls and join them together, say by joining based on the same line_num.

I'd think if you would want to add a further column with varying values to your "insert select", then

the separate table form Justin's sample (enhanced with that additional column) or
a query on data from another source table

might be easier solutions.

Just to understand: Are the values 4001, 4003 ... data from an existing different table? If so, I would generally recommend to build the INSERT SELECT based on a query that joins over that table, instead of a separate list.

(09 Oct '14, 03:35) Volker Barth

Thanks for the answer. The values are not data from any existing table...

(09 Oct '14, 04:43) Rolle

Replies hidden

Very neat Volker - I tend to forget about sa_split_list()!

(09 Oct '14, 05:32) Justin Willey

1

OK, so then you might use sa_split_list as following (with a fictional new column c_var2 with values a, b, c, d, e, f):

INSERT INTO tblcustomer (c_id, c_type, c_kont, c_var, c_var2) 
SELECT k_id, 1, 1, S1.row_value, S2.row_value
FROM tblKont CROSS JOIN
 (sa_split_list('4001,4003,4006,4009,4020,4032') S1
  INNER JOIN sa_split_list('a,b,c,d,e,f') S2
    ON S1.line_num = S2.line_num)
WHERE tblkont.k_id_nr IS NULL;

For each filtered row from tblKont there will be six according rows with one of 4001/a, 4003/b, 4006/c etc. as values for the added columns.

(09 Oct '14, 05:54) Volker Barth

A little last question. If I want some value will be null in the table, what do I do? I want null and not an empty string.

I can not do this ..?

INSERT INTO tblcustomer (c_id, c_type, c_kont, c_var, c_var2) 
SELECT k_id, 1, 1, S1.row_value, S2.row_value
FROM tblKont CROSS JOIN
 (sa_split_list('4001,4003,4006,4009,4020,4032') S1
  INNER JOIN sa_split_list('a,Null,c,d,Null,f') S2
    ON S1.line_num = S2.line_num)
WHERE tblkont.k_id_nr IS NULL;

(09 Oct '14, 12:10) Rolle

Replies hidden

I guess that's basically a question on its own - asked here...

(10 Oct '14, 03:43) Volker Barth

Thanks again.

(10 Oct '14, 06:59) Rolle

Just to add: Ivan has shown (and explained!) further methods like OPENSTRING() and sp_parse_json() in his answer on the interim FAQ...

(16 Oct '14, 06:38) Volker Barth

More comments hidden

showing 5 of 9 show all flat view

Answer 2

What about creating a table with values of c_var that you want (untested):

create table c_vars(c_var int);
insert into c_vars(c_var) values (4001);
insert into c_vars(c_var) values (4003);
insert into c_vars(c_var) values (4006);
insert into c_vars(c_var) values (4009);
insert into c_vars(c_var) values (4020);
insert into c_vars(c_var) values (4032);

then cross join to it:

    SELECT k_id, 1, 1, c_var
    FROM tblKont cross join c_vars 
    WHERE tblkont.k_id_nr Is Null;

the result should look like what you want.

BTW be careful with your null handling - you say: each row that has tblkont.k_id_nr zero but your query says tblkont.k_id_nr Is Null - not the same thing at all.

SAP SQL Anywhere

Insert multiple rows at once with a single insert command.

Follow this question

Related questions