How to copy a column value from one table and store the same value into another table as a row

Question

enter image description hereHow to copy a column value from one table and store the same value into another table as a row .

I have two tables one is firsttbl and second one is testtbl. then first table have more records and second table need copy the same value in first table.

firsttbl original record below enter image description here

CORRELATION_ID                      NAME            VALUE            TYPE             OBJECT_ID   ARCHIVE_FLAG  ARCHIVE_DATE WF_ID          REC_TIME     KEY_ID VALUE_UPPER
43344255015b9b192916node1   TransactionSenderID    SANMINACORT  DOCUMENT    14804515b9b1924f9node1  -1              null    1912341 2017-04-23 08:56:09.0   20  SANMINACORT
43391255115b9b192916node1   TransactionReceiverID    4017395800 DOCUMENT    14804515b9b1924f9node1  -1              null    1912341 2017-04-23 08:56:09.0   21  4017395800
43376255215b9b192916node1   Level                   Transaction DOCUMENT    14804515b9b1924f9node1  -1              null    1912341 2017-04-23 08:56:09.0   41  TRANSACTION
43399255315b9b192916node1   GroupSenderID         SANMINACORT   DOCUMENT    14804515b9b1924f9node1  -1              null    1912341 2017-04-23 08:56:09.0   28  SANMINACORT
43356255415b9b192916node1   GroupReceiverID        4017395800   DOCUMENT    14804515b9b1924f9node1  -1              null    1912341 2017-04-23 08:56:09.0   30  4017395800

2nd table only created not yet stored in any value.

 TransactionSenderID   TransactionReceiverID   Level   GroupSenderID   GroupReceiverID

second table coulmn names are already stored in firsttbl under NAME coulmn.

I'm going to copy records from firsttbl table so the firsttbl column name VALUE all the 5 values are need to stored in second table(testtbl) as a row. This the query Im trying but not yet success.

INSERT INTO testtbl(TransactionSenderID,TransactionReceiverID,Level,GroupSenderID,GroupReceiverID) Select value from firsttbl;

This is the error coming in PostgreSQL.

ERROR: INSERT has more target columns than expressions LINE 1: INSERT INTO testtbl(TransactionSenderID,TransactionReceiverI... ^

********** Error **********

ERROR: INSERT has more target columns than expressions SQL state: 42601 Character: 41

All the related records have the same object_id? Can you supply the table definitions and tidy? — P.Salmon

P.Salmon P.Salmon · Accepted Answer · 2017-05-10T07:51:47

This looks like a pivot/group aggregation question. I don't have postgresql but since the question is tagged mysql here's a mysql solution (if the quesion is postgresql the approach should be similar)

drop table if exists t;
create table t (
CORRELATION_ID varchar(30), NAME varchar(30),VALUe varchar(30), TYPE VARCHAR(30), OBJECT_ID VARCHAR(30), ARCHIVE_FLAG VARCHAR(30), 
ARCHIVE_DATE datetime, WF_ID int, REC_TIME datetime, KEY_ID int, VALUE_UPPER varchar(20));
insert into t values
('43344255015b9b192916node1', 'TransactionSenderID' ,'SANMINACORT' ,'DOCUMENT' ,'14804515b9b1924f9node1', '-1' ,null ,1912341 ,'2017-04-23 08:56:09.0', 20 ,'SANMINACORT'), 
('43391255115b9b192916node1', 'TransactionReceiverID' ,'4017395800' ,'DOCUMENT' ,'14804515b9b1924f9node1', '-1' ,null ,1912341 ,'2017-04-23 08:56:09.0' ,21 ,'4017395800' ),
('43376255215b9b192916node1', 'Level', 'Transaction', 'DOCUMENT' ,'14804515b9b1924f9node1' ,'-1' ,null ,1912341 ,'2017-04-23 08:56:09.0', 41 ,'TRANSACTION' ),
('43399255315b9b192916node1', 'GroupSenderID' ,'SANMINACORT', 'DOCUMENT' ,'14804515b9b1924f9node1' ,'-1' ,null ,1912341 ,'2017-04-23 08:56:09.0' ,28, 'SANMINACORT'), 
('43356255415b9b192916node1', 'GroupReceiverID', '4017395800', 'DOCUMENT' ,'14804515b9b1924f9node1' ,'-1' ,null ,1912341 ,'2017-04-23 08:56:09.0',30, '4017395800');

drop table if exists t2;
create table t2(wf_id int,TransactionSenderID varchar(20),  TransactionReceiverID varchar(20),  Level varchar(20),  GroupSenderID varchar(20),  GroupReceiverID varchar(20));

insert into t2
select wf_id, 
        max(case when name = 'TransactionSenderID' then value else null end),
        max(case when name = 'TransactionReceiverID' then value else null end),
        max(case when name = 'Level' then value else null end),
        max(case when name = 'GroupSenderID' then value else null end),
        max(case when name = 'GroupReceiverID' then value else null end)
from t 
group by wf_id;

select * from t2;

MariaDB [sandbox]> select * from t2;
+---------+---------------------+-----------------------+-------------+---------------+-----------------+
| wf_id   | TransactionSenderID | TransactionReceiverID | Level       | GroupSenderID | GroupReceiverID |
+---------+---------------------+-----------------------+-------------+---------------+-----------------+
| 1912341 | SANMINACORT         | 4017395800            | Transaction | SANMINACORT   | 4017395800      |
+---------+---------------------+-----------------------+-------------+---------------+-----------------+
1 row in set (0.00 sec)

How to copy a column value from one table and store the same value into another table as a row

1 Answers