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.
|
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
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: |