The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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

asked 08 Oct '14, 14:08

Rolle's gravatar image

Rolle
379243342
accept rate: 0%

edited 08 Oct '14, 14:12


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%20Barth's gravatar image

Volker Barth
30.9k309457667
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

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.

permanent link

answered 08 Oct '14, 14:34

Justin%20Willey's gravatar image

Justin Willey
6.8k110144212
accept rate: 20%

edited 08 Oct '14, 14:41

Your answer
toggle preview

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

Question tags:

×5

question asked: 08 Oct '14, 14:08

question was seen: 861 times

last updated: 16 Oct '14, 06:38