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
379213042
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.3k300452659
accept rate: 32%

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.7k108137208
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: 790 times

last updated: 16 Oct '14, 06:38