Mixing
Need to load data from one table to another
0
Initial row data is there in below table-
create table offer_row_data (
customer_key char(20),
offer1_id char(20),
offer1_cd char(20),
offer1_brand_nm char(20),
offer2_id char(20),
offer2_cd char(20),
offer2_brand_nm char(20),
offer3_id char(20),
offer3_cd char(20),
offer3_brand_nm char(20),
offer4_id char(20),
offer4_cd char(20),
offer4_brand_nm char(20)
);
I need to transform this and load in below table...
create table offer_data (
offer_key INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
offer_id char(20) NOT NULL,
offer_cd char(20) NOT NULL,
offer_brand_nm char(20));
here is some random data-
INSERT INTO offer_row_data VALUES ('1','offer_id_1a','offe3_cd_1a','offer_nm_1a','offer_id_1b','offe3_cd_1b','offer_nm_1b','offer_id_1c','offe3_cd_1c','offer_nm_1c','offer_id_1d','offe3_cd_1d','offer_nm_1d');
INSERT INTO offer_row_data VALUES ('2','offer_id_2a','offe3_cd_2a','offer_nm_2a','offer_id_2b','offe3_cd_2b','offer_nm_2b','offer_id_2c','offe3_cd_2c','offer_nm_2c','offer_id_2d','offe3_cd_2d','offer_nm_2d');
INSERT INTO offer_row_data VALUES ('3','offer_id_3a','offe3_cd_3a','offer_nm_3a','offer_id_3b','offe3_cd_3b','offer_nm_3b','offer_id_3c','offe3_cd_3c','offer_nm_3c','offer_id_3d','offe3_cd_3d','offer_nm_3d');
here is the solution of mine -
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_row_data)
since I'm having 25 million of the dataset so it will be a performance burden and expecting a more efficient solution
Example:
Input:
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| customer_key | offer1_id | offer1_cd | offer1_brand_nm | offer2_id | offer2_cd | offer2_brand_nm | offer3_id | offer3_cd | offer3_brand_nm | offer4_id | offer4_cd | offer4_brand_nm |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a | offer_id_1b | offe3_cd_1b | offer_nm_1b | offer_id_1c | offe3_cd_1c | offer_nm_1c | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a | offer_id_2b | offe3_cd_2b | offer_nm_2b | offer_id_2c | offe3_cd_2c | offer_nm_2c | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a | offer_id_3b | offe3_cd_3b | offer_nm_3b | offer_id_3c | offe3_cd_3c | offer_nm_3c | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
Expected output:
+-----------+-------------+-------------+----------------+
| offer_key | offer_id | offer_cd | offer_brand_nm |
+-----------+-------------+-------------+----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a |
| 4 | offer_id_1b | offe3_cd_1b | offer_nm_1b |
| 5 | offer_id_2b | offe3_cd_2b | offer_nm_2b |
| 6 | offer_id_3b | offe3_cd_3b | offer_nm_3b |
| 7 | offer_id_1c | offe3_cd_1c | offer_nm_1c |
| 8 | offer_id_2c | offe3_cd_2c | offer_nm_2c |
| 9 | offer_id_3c | offe3_cd_3c | offer_nm_3c |
| 10 | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 11 | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 12 | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+-----------+-------------+-------------+----------------+
mysql data-warehouse
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
|
show 3 more comments
0
Initial row data is there in below table-
create table offer_row_data (
customer_key char(20),
offer1_id char(20),
offer1_cd char(20),
offer1_brand_nm char(20),
offer2_id char(20),
offer2_cd char(20),
offer2_brand_nm char(20),
offer3_id char(20),
offer3_cd char(20),
offer3_brand_nm char(20),
offer4_id char(20),
offer4_cd char(20),
offer4_brand_nm char(20)
);
I need to transform this and load in below table...
create table offer_data (
offer_key INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
offer_id char(20) NOT NULL,
offer_cd char(20) NOT NULL,
offer_brand_nm char(20));
here is some random data-
INSERT INTO offer_row_data VALUES ('1','offer_id_1a','offe3_cd_1a','offer_nm_1a','offer_id_1b','offe3_cd_1b','offer_nm_1b','offer_id_1c','offe3_cd_1c','offer_nm_1c','offer_id_1d','offe3_cd_1d','offer_nm_1d');
INSERT INTO offer_row_data VALUES ('2','offer_id_2a','offe3_cd_2a','offer_nm_2a','offer_id_2b','offe3_cd_2b','offer_nm_2b','offer_id_2c','offe3_cd_2c','offer_nm_2c','offer_id_2d','offe3_cd_2d','offer_nm_2d');
INSERT INTO offer_row_data VALUES ('3','offer_id_3a','offe3_cd_3a','offer_nm_3a','offer_id_3b','offe3_cd_3b','offer_nm_3b','offer_id_3c','offe3_cd_3c','offer_nm_3c','offer_id_3d','offe3_cd_3d','offer_nm_3d');
here is the solution of mine -
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_row_data)
since I'm having 25 million of the dataset so it will be a performance burden and expecting a more efficient solution
Example:
Input:
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| customer_key | offer1_id | offer1_cd | offer1_brand_nm | offer2_id | offer2_cd | offer2_brand_nm | offer3_id | offer3_cd | offer3_brand_nm | offer4_id | offer4_cd | offer4_brand_nm |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a | offer_id_1b | offe3_cd_1b | offer_nm_1b | offer_id_1c | offe3_cd_1c | offer_nm_1c | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a | offer_id_2b | offe3_cd_2b | offer_nm_2b | offer_id_2c | offe3_cd_2c | offer_nm_2c | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a | offer_id_3b | offe3_cd_3b | offer_nm_3b | offer_id_3c | offe3_cd_3c | offer_nm_3c | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
Expected output:
+-----------+-------------+-------------+----------------+
| offer_key | offer_id | offer_cd | offer_brand_nm |
+-----------+-------------+-------------+----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a |
| 4 | offer_id_1b | offe3_cd_1b | offer_nm_1b |
| 5 | offer_id_2b | offe3_cd_2b | offer_nm_2b |
| 6 | offer_id_3b | offe3_cd_3b | offer_nm_3b |
| 7 | offer_id_1c | offe3_cd_1c | offer_nm_1c |
| 8 | offer_id_2c | offe3_cd_2c | offer_nm_2c |
| 9 | offer_id_3c | offe3_cd_3c | offer_nm_3c |
| 10 | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 11 | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 12 | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+-----------+-------------+-------------+----------------+
mysql data-warehouse
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
Does the order of theoffer_keyreally matter to you?
– Tim Biegeleisen
Nov 20 '18 at 10:51
No, it doesn't matter. just it should load data to the target table correctly...
– Vipul Kumar
Nov 20 '18 at 10:59
I can't think of a more efficient solution than what you already have, except that you might not need to useDISTINCT, unless you expect duplicate values. Anyway, theUNIONshould already remove duplicates.
– Tim Biegeleisen
Nov 20 '18 at 11:01
I also realized it a later point of time if I'm using union and it is a set operation then distinct can be skipped... but looking for some other way to do it so that it should not create any performance issues...
– Vipul Kumar
Nov 20 '18 at 11:07
Well how often do you need to run this query? As a one off thing, is your current solution a significant problem?
– Tim Biegeleisen
Nov 20 '18 at 11:10
|
show 3 more comments
0
0
0
Initial row data is there in below table-
create table offer_row_data (
customer_key char(20),
offer1_id char(20),
offer1_cd char(20),
offer1_brand_nm char(20),
offer2_id char(20),
offer2_cd char(20),
offer2_brand_nm char(20),
offer3_id char(20),
offer3_cd char(20),
offer3_brand_nm char(20),
offer4_id char(20),
offer4_cd char(20),
offer4_brand_nm char(20)
);
I need to transform this and load in below table...
create table offer_data (
offer_key INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
offer_id char(20) NOT NULL,
offer_cd char(20) NOT NULL,
offer_brand_nm char(20));
here is some random data-
INSERT INTO offer_row_data VALUES ('1','offer_id_1a','offe3_cd_1a','offer_nm_1a','offer_id_1b','offe3_cd_1b','offer_nm_1b','offer_id_1c','offe3_cd_1c','offer_nm_1c','offer_id_1d','offe3_cd_1d','offer_nm_1d');
INSERT INTO offer_row_data VALUES ('2','offer_id_2a','offe3_cd_2a','offer_nm_2a','offer_id_2b','offe3_cd_2b','offer_nm_2b','offer_id_2c','offe3_cd_2c','offer_nm_2c','offer_id_2d','offe3_cd_2d','offer_nm_2d');
INSERT INTO offer_row_data VALUES ('3','offer_id_3a','offe3_cd_3a','offer_nm_3a','offer_id_3b','offe3_cd_3b','offer_nm_3b','offer_id_3c','offe3_cd_3c','offer_nm_3c','offer_id_3d','offe3_cd_3d','offer_nm_3d');
here is the solution of mine -
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_row_data)
since I'm having 25 million of the dataset so it will be a performance burden and expecting a more efficient solution
Example:
Input:
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| customer_key | offer1_id | offer1_cd | offer1_brand_nm | offer2_id | offer2_cd | offer2_brand_nm | offer3_id | offer3_cd | offer3_brand_nm | offer4_id | offer4_cd | offer4_brand_nm |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a | offer_id_1b | offe3_cd_1b | offer_nm_1b | offer_id_1c | offe3_cd_1c | offer_nm_1c | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a | offer_id_2b | offe3_cd_2b | offer_nm_2b | offer_id_2c | offe3_cd_2c | offer_nm_2c | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a | offer_id_3b | offe3_cd_3b | offer_nm_3b | offer_id_3c | offe3_cd_3c | offer_nm_3c | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
Expected output:
+-----------+-------------+-------------+----------------+
| offer_key | offer_id | offer_cd | offer_brand_nm |
+-----------+-------------+-------------+----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a |
| 4 | offer_id_1b | offe3_cd_1b | offer_nm_1b |
| 5 | offer_id_2b | offe3_cd_2b | offer_nm_2b |
| 6 | offer_id_3b | offe3_cd_3b | offer_nm_3b |
| 7 | offer_id_1c | offe3_cd_1c | offer_nm_1c |
| 8 | offer_id_2c | offe3_cd_2c | offer_nm_2c |
| 9 | offer_id_3c | offe3_cd_3c | offer_nm_3c |
| 10 | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 11 | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 12 | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+-----------+-------------+-------------+----------------+
mysql data-warehouse
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
Initial row data is there in below table-
create table offer_row_data (
customer_key char(20),
offer1_id char(20),
offer1_cd char(20),
offer1_brand_nm char(20),
offer2_id char(20),
offer2_cd char(20),
offer2_brand_nm char(20),
offer3_id char(20),
offer3_cd char(20),
offer3_brand_nm char(20),
offer4_id char(20),
offer4_cd char(20),
offer4_brand_nm char(20)
);
I need to transform this and load in below table...
create table offer_data (
offer_key INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
offer_id char(20) NOT NULL,
offer_cd char(20) NOT NULL,
offer_brand_nm char(20));
here is some random data-
INSERT INTO offer_row_data VALUES ('1','offer_id_1a','offe3_cd_1a','offer_nm_1a','offer_id_1b','offe3_cd_1b','offer_nm_1b','offer_id_1c','offe3_cd_1c','offer_nm_1c','offer_id_1d','offe3_cd_1d','offer_nm_1d');
INSERT INTO offer_row_data VALUES ('2','offer_id_2a','offe3_cd_2a','offer_nm_2a','offer_id_2b','offe3_cd_2b','offer_nm_2b','offer_id_2c','offe3_cd_2c','offer_nm_2c','offer_id_2d','offe3_cd_2d','offer_nm_2d');
INSERT INTO offer_row_data VALUES ('3','offer_id_3a','offe3_cd_3a','offer_nm_3a','offer_id_3b','offe3_cd_3b','offer_nm_3b','offer_id_3c','offe3_cd_3c','offer_nm_3c','offer_id_3d','offe3_cd_3d','offer_nm_3d');
here is the solution of mine -
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_row_data)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_row_data)
since I'm having 25 million of the dataset so it will be a performance burden and expecting a more efficient solution
Example:
Input:
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| customer_key | offer1_id | offer1_cd | offer1_brand_nm | offer2_id | offer2_cd | offer2_brand_nm | offer3_id | offer3_cd | offer3_brand_nm | offer4_id | offer4_cd | offer4_brand_nm |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a | offer_id_1b | offe3_cd_1b | offer_nm_1b | offer_id_1c | offe3_cd_1c | offer_nm_1c | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a | offer_id_2b | offe3_cd_2b | offer_nm_2b | offer_id_2c | offe3_cd_2c | offer_nm_2c | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a | offer_id_3b | offe3_cd_3b | offer_nm_3b | offer_id_3c | offe3_cd_3c | offer_nm_3c | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+--------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+-------------+-------------+-----------------+
Expected output:
+-----------+-------------+-------------+----------------+
| offer_key | offer_id | offer_cd | offer_brand_nm |
+-----------+-------------+-------------+----------------+
| 1 | offer_id_1a | offe3_cd_1a | offer_nm_1a |
| 2 | offer_id_2a | offe3_cd_2a | offer_nm_2a |
| 3 | offer_id_3a | offe3_cd_3a | offer_nm_3a |
| 4 | offer_id_1b | offe3_cd_1b | offer_nm_1b |
| 5 | offer_id_2b | offe3_cd_2b | offer_nm_2b |
| 6 | offer_id_3b | offe3_cd_3b | offer_nm_3b |
| 7 | offer_id_1c | offe3_cd_1c | offer_nm_1c |
| 8 | offer_id_2c | offe3_cd_2c | offer_nm_2c |
| 9 | offer_id_3c | offe3_cd_3c | offer_nm_3c |
| 10 | offer_id_1d | offe3_cd_1d | offer_nm_1d |
| 11 | offer_id_2d | offe3_cd_2d | offer_nm_2d |
| 12 | offer_id_3d | offe3_cd_3d | offer_nm_3d |
+-----------+-------------+-------------+----------------+
mysql data-warehouse
mysql data-warehouse
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
asked Nov 20 '18 at 10:47
Vipul KumarVipul Kumar
14
14
Does the order of theoffer_keyreally matter to you?
– Tim Biegeleisen
Nov 20 '18 at 10:51
No, it doesn't matter. just it should load data to the target table correctly...
– Vipul Kumar
Nov 20 '18 at 10:59
I can't think of a more efficient solution than what you already have, except that you might not need to useDISTINCT, unless you expect duplicate values. Anyway, theUNIONshould already remove duplicates.
– Tim Biegeleisen
Nov 20 '18 at 11:01
I also realized it a later point of time if I'm using union and it is a set operation then distinct can be skipped... but looking for some other way to do it so that it should not create any performance issues...
– Vipul Kumar
Nov 20 '18 at 11:07
Well how often do you need to run this query? As a one off thing, is your current solution a significant problem?
– Tim Biegeleisen
Nov 20 '18 at 11:10
|
show 3 more comments
Does the order of theoffer_keyreally matter to you?
– Tim Biegeleisen
Nov 20 '18 at 10:51
No, it doesn't matter. just it should load data to the target table correctly...
– Vipul Kumar
Nov 20 '18 at 10:59
I can't think of a more efficient solution than what you already have, except that you might not need to useDISTINCT, unless you expect duplicate values. Anyway, theUNIONshould already remove duplicates.
– Tim Biegeleisen
Nov 20 '18 at 11:01
I also realized it a later point of time if I'm using union and it is a set operation then distinct can be skipped... but looking for some other way to do it so that it should not create any performance issues...
– Vipul Kumar
Nov 20 '18 at 11:07
Well how often do you need to run this query? As a one off thing, is your current solution a significant problem?
– Tim Biegeleisen
Nov 20 '18 at 11:10
Does the order of the
offer_key really matter to you?– Tim Biegeleisen
Nov 20 '18 at 10:51
Does the order of the
offer_key really matter to you?– Tim Biegeleisen
Nov 20 '18 at 10:51
No, it doesn't matter. just it should load data to the target table correctly...
– Vipul Kumar
Nov 20 '18 at 10:59
No, it doesn't matter. just it should load data to the target table correctly...
– Vipul Kumar
Nov 20 '18 at 10:59
I can't think of a more efficient solution than what you already have, except that you might not need to use
DISTINCT, unless you expect duplicate values. Anyway, the UNION should already remove duplicates.– Tim Biegeleisen
Nov 20 '18 at 11:01
I can't think of a more efficient solution than what you already have, except that you might not need to use
DISTINCT, unless you expect duplicate values. Anyway, the UNION should already remove duplicates.– Tim Biegeleisen
Nov 20 '18 at 11:01
I also realized it a later point of time if I'm using union and it is a set operation then distinct can be skipped... but looking for some other way to do it so that it should not create any performance issues...
– Vipul Kumar
Nov 20 '18 at 11:07
I also realized it a later point of time if I'm using union and it is a set operation then distinct can be skipped... but looking for some other way to do it so that it should not create any performance issues...
– Vipul Kumar
Nov 20 '18 at 11:07
Well how often do you need to run this query? As a one off thing, is your current solution a significant problem?
– Tim Biegeleisen
Nov 20 '18 at 11:10
Well how often do you need to run this query? As a one off thing, is your current solution a significant problem?
– Tim Biegeleisen
Nov 20 '18 at 11:10
|
show 3 more comments
2 Answers
2
active
oldest
votes
0
If you use a CTE, it reads data only once instead of 4 times in your original SQL and it might be faster.
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
WITH offer_CTE as (SELECT * FROM offer_row_data)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_CTE)
Please let me know if this solves your performance issue.
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
I think you missed something. The query is giving an error, could you please reframe the same.
– Vipul Kumar
Nov 21 '18 at 13:56
what is the error message? Also what I the version of your mysql? mysql started supporting CTE's after version 8
– mdem7
Nov 21 '18 at 18:01
I'm using MySQL 5.7. can you help me out writing a MySQL 5.7 equivalent of above query...
– Vipul Kumar
Nov 22 '18 at 4:33
add a comment |
0
I would pick this as simple and possibly the fastest, and it works if you are not using MySQL 8.0 / MariaDB 10.2 (which have CTE):
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer1_id, offer1_cd, offer1_brand_nm
FROM wide_table;
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer2_id, offer2_cd, offer2_brand_nm
FROM wide_table;
INSERT ... 3...
INSERT ... 4...
If there are "up to 4" offers in the rows, I suggest you add this on to SELECT #2:
WHERE offer2_id IS NOT NULL
OR offer2_cd IS NOT NULL
OR offer2_brand_nm IS NOT NULL
(And do similarly for the other selects.)
DISTINCT will slow things down, but do it if you need to. Alternatively, use INSERT IGNORE and have some kind of UNIQUE key ready to catch dups.
You might want to rethink the need for an AUTO_INCREMENT on the new table. Perhaps one of (or a combination of) the new columns could be the 'natural' PRIMARY KEY?
This would also be a good time to check the datatypes. Maybe you don't need an 8-byte BIGINT, but could use a 4-byte INT or 3-byte MEDIUMINT. Are the char(20) columns really fixed length? Are they utf8? VARCHAR(20) with a suitable CHARACTER SET might be significantly better for space and performance.
Once you get the table loaded, toss the old table. It is bad practice to have an array spread across multiple columns.
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391319%2fneed-to-load-data-from-one-table-to-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
If you use a CTE, it reads data only once instead of 4 times in your original SQL and it might be faster.
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
WITH offer_CTE as (SELECT * FROM offer_row_data)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_CTE)
Please let me know if this solves your performance issue.
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
I think you missed something. The query is giving an error, could you please reframe the same.
– Vipul Kumar
Nov 21 '18 at 13:56
what is the error message? Also what I the version of your mysql? mysql started supporting CTE's after version 8
– mdem7
Nov 21 '18 at 18:01
I'm using MySQL 5.7. can you help me out writing a MySQL 5.7 equivalent of above query...
– Vipul Kumar
Nov 22 '18 at 4:33
add a comment |
0
If you use a CTE, it reads data only once instead of 4 times in your original SQL and it might be faster.
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
WITH offer_CTE as (SELECT * FROM offer_row_data)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_CTE)
Please let me know if this solves your performance issue.
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
I think you missed something. The query is giving an error, could you please reframe the same.
– Vipul Kumar
Nov 21 '18 at 13:56
what is the error message? Also what I the version of your mysql? mysql started supporting CTE's after version 8
– mdem7
Nov 21 '18 at 18:01
I'm using MySQL 5.7. can you help me out writing a MySQL 5.7 equivalent of above query...
– Vipul Kumar
Nov 22 '18 at 4:33
add a comment |
0
0
0
If you use a CTE, it reads data only once instead of 4 times in your original SQL and it might be faster.
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
WITH offer_CTE as (SELECT * FROM offer_row_data)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_CTE)
Please let me know if this solves your performance issue.
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
If you use a CTE, it reads data only once instead of 4 times in your original SQL and it might be faster.
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
WITH offer_CTE as (SELECT * FROM offer_row_data)
(SELECT distinct offer1_id, offer1_cd, offer1_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer2_id, offer2_cd, offer2_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer3_id, offer3_cd, offer3_brand_nm FROM offer_CTE)
UNION
(SELECT distinct offer4_id, offer4_cd, offer4_brand_nm FROM offer_CTE)
Please let me know if this solves your performance issue.
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
answered Nov 21 '18 at 0:18
mdem7mdem7
485310
485310
I think you missed something. The query is giving an error, could you please reframe the same.
– Vipul Kumar
Nov 21 '18 at 13:56
what is the error message? Also what I the version of your mysql? mysql started supporting CTE's after version 8
– mdem7
Nov 21 '18 at 18:01
I'm using MySQL 5.7. can you help me out writing a MySQL 5.7 equivalent of above query...
– Vipul Kumar
Nov 22 '18 at 4:33
add a comment |
I think you missed something. The query is giving an error, could you please reframe the same.
– Vipul Kumar
Nov 21 '18 at 13:56
what is the error message? Also what I the version of your mysql? mysql started supporting CTE's after version 8
– mdem7
Nov 21 '18 at 18:01
I'm using MySQL 5.7. can you help me out writing a MySQL 5.7 equivalent of above query...
– Vipul Kumar
Nov 22 '18 at 4:33
I think you missed something. The query is giving an error, could you please reframe the same.
– Vipul Kumar
Nov 21 '18 at 13:56
I think you missed something. The query is giving an error, could you please reframe the same.
– Vipul Kumar
Nov 21 '18 at 13:56
what is the error message? Also what I the version of your mysql? mysql started supporting CTE's after version 8
– mdem7
Nov 21 '18 at 18:01
what is the error message? Also what I the version of your mysql? mysql started supporting CTE's after version 8
– mdem7
Nov 21 '18 at 18:01
I'm using MySQL 5.7. can you help me out writing a MySQL 5.7 equivalent of above query...
– Vipul Kumar
Nov 22 '18 at 4:33
I'm using MySQL 5.7. can you help me out writing a MySQL 5.7 equivalent of above query...
– Vipul Kumar
Nov 22 '18 at 4:33
add a comment |
0
I would pick this as simple and possibly the fastest, and it works if you are not using MySQL 8.0 / MariaDB 10.2 (which have CTE):
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer1_id, offer1_cd, offer1_brand_nm
FROM wide_table;
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer2_id, offer2_cd, offer2_brand_nm
FROM wide_table;
INSERT ... 3...
INSERT ... 4...
If there are "up to 4" offers in the rows, I suggest you add this on to SELECT #2:
WHERE offer2_id IS NOT NULL
OR offer2_cd IS NOT NULL
OR offer2_brand_nm IS NOT NULL
(And do similarly for the other selects.)
DISTINCT will slow things down, but do it if you need to. Alternatively, use INSERT IGNORE and have some kind of UNIQUE key ready to catch dups.
You might want to rethink the need for an AUTO_INCREMENT on the new table. Perhaps one of (or a combination of) the new columns could be the 'natural' PRIMARY KEY?
This would also be a good time to check the datatypes. Maybe you don't need an 8-byte BIGINT, but could use a 4-byte INT or 3-byte MEDIUMINT. Are the char(20) columns really fixed length? Are they utf8? VARCHAR(20) with a suitable CHARACTER SET might be significantly better for space and performance.
Once you get the table loaded, toss the old table. It is bad practice to have an array spread across multiple columns.
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
add a comment |
0
I would pick this as simple and possibly the fastest, and it works if you are not using MySQL 8.0 / MariaDB 10.2 (which have CTE):
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer1_id, offer1_cd, offer1_brand_nm
FROM wide_table;
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer2_id, offer2_cd, offer2_brand_nm
FROM wide_table;
INSERT ... 3...
INSERT ... 4...
If there are "up to 4" offers in the rows, I suggest you add this on to SELECT #2:
WHERE offer2_id IS NOT NULL
OR offer2_cd IS NOT NULL
OR offer2_brand_nm IS NOT NULL
(And do similarly for the other selects.)
DISTINCT will slow things down, but do it if you need to. Alternatively, use INSERT IGNORE and have some kind of UNIQUE key ready to catch dups.
You might want to rethink the need for an AUTO_INCREMENT on the new table. Perhaps one of (or a combination of) the new columns could be the 'natural' PRIMARY KEY?
This would also be a good time to check the datatypes. Maybe you don't need an 8-byte BIGINT, but could use a 4-byte INT or 3-byte MEDIUMINT. Are the char(20) columns really fixed length? Are they utf8? VARCHAR(20) with a suitable CHARACTER SET might be significantly better for space and performance.
Once you get the table loaded, toss the old table. It is bad practice to have an array spread across multiple columns.
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
add a comment |
0
0
0
I would pick this as simple and possibly the fastest, and it works if you are not using MySQL 8.0 / MariaDB 10.2 (which have CTE):
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer1_id, offer1_cd, offer1_brand_nm
FROM wide_table;
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer2_id, offer2_cd, offer2_brand_nm
FROM wide_table;
INSERT ... 3...
INSERT ... 4...
If there are "up to 4" offers in the rows, I suggest you add this on to SELECT #2:
WHERE offer2_id IS NOT NULL
OR offer2_cd IS NOT NULL
OR offer2_brand_nm IS NOT NULL
(And do similarly for the other selects.)
DISTINCT will slow things down, but do it if you need to. Alternatively, use INSERT IGNORE and have some kind of UNIQUE key ready to catch dups.
You might want to rethink the need for an AUTO_INCREMENT on the new table. Perhaps one of (or a combination of) the new columns could be the 'natural' PRIMARY KEY?
This would also be a good time to check the datatypes. Maybe you don't need an 8-byte BIGINT, but could use a 4-byte INT or 3-byte MEDIUMINT. Are the char(20) columns really fixed length? Are they utf8? VARCHAR(20) with a suitable CHARACTER SET might be significantly better for space and performance.
Once you get the table loaded, toss the old table. It is bad practice to have an array spread across multiple columns.
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
I would pick this as simple and possibly the fastest, and it works if you are not using MySQL 8.0 / MariaDB 10.2 (which have CTE):
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer1_id, offer1_cd, offer1_brand_nm
FROM wide_table;
INSERT INTO offer_data (offer_id,offer_cd,offer_brand_nm)
SELECT offer2_id, offer2_cd, offer2_brand_nm
FROM wide_table;
INSERT ... 3...
INSERT ... 4...
If there are "up to 4" offers in the rows, I suggest you add this on to SELECT #2:
WHERE offer2_id IS NOT NULL
OR offer2_cd IS NOT NULL
OR offer2_brand_nm IS NOT NULL
(And do similarly for the other selects.)
DISTINCT will slow things down, but do it if you need to. Alternatively, use INSERT IGNORE and have some kind of UNIQUE key ready to catch dups.
You might want to rethink the need for an AUTO_INCREMENT on the new table. Perhaps one of (or a combination of) the new columns could be the 'natural' PRIMARY KEY?
This would also be a good time to check the datatypes. Maybe you don't need an 8-byte BIGINT, but could use a 4-byte INT or 3-byte MEDIUMINT. Are the char(20) columns really fixed length? Are they utf8? VARCHAR(20) with a suitable CHARACTER SET might be significantly better for space and performance.
Once you get the table loaded, toss the old table. It is bad practice to have an array spread across multiple columns.
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
edited Dec 30 '18 at 1:53
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
answered Dec 30 '18 at 1:46
Rick JamesRick James
67.1k55899
67.1k55899
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391319%2fneed-to-load-data-from-one-table-to-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Does the order of the
offer_keyreally matter to you?– Tim Biegeleisen
Nov 20 '18 at 10:51
No, it doesn't matter. just it should load data to the target table correctly...
– Vipul Kumar
Nov 20 '18 at 10:59
I can't think of a more efficient solution than what you already have, except that you might not need to use
DISTINCT, unless you expect duplicate values. Anyway, theUNIONshould already remove duplicates.– Tim Biegeleisen
Nov 20 '18 at 11:01
I also realized it a later point of time if I'm using union and it is a set operation then distinct can be skipped... but looking for some other way to do it so that it should not create any performance issues...
– Vipul Kumar
Nov 20 '18 at 11:07
Well how often do you need to run this query? As a one off thing, is your current solution a significant problem?
– Tim Biegeleisen
Nov 20 '18 at 11:10