I want to insert in a table with a select from another.
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.
asked 08 Oct '14, 14:08 Rolle |
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:
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
answered 09 Oct '14, 03:05 Volker Barth 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
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 ..?
(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
|
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: answered 08 Oct '14, 14:34 Justin Willey |