Insert multiple rows at once with a single insert command.

[former_SQLA_member1694869]

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

[VolkerBarth · Accepted solution]

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;

[former_SQLA_member1694869 · Accepted solution]

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

[VolkerBarth · Accepted solution]

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.

[former_SQLA_member1694869 · Accepted solution]

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

[justin_willey · Accepted solution]

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

[VolkerBarth · Accepted solution]

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.

[former_SQLA_member1694869 · Accepted solution]

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;

[VolkerBarth · Accepted solution]

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

[former_SQLA_member1694869 · Accepted solution]

Thanks again.

[VolkerBarth · Accepted solution]

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

[justin_willey]

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.