Append the specific column in another existing table using SQL QUERY

Question

I know it sounds easy when appending a entire row to new table, but this is different

Question 1, state to combine the Table 1 and Table 2 and keep duplicate value, to create a new table, I have used the following query:

CREATE TABLE EMP_TBL_3
(
    EMP_ID int,
    Name varchar(255),
    DOJ int
);

INSERT INTO EMP_TBL_3 (EMP_ID, Name, DOJ)
   SELECT * FROM EMP_TBL_1 
   UNION ALL
   SELECT * FROM EMP_TBL_2;

Results:

Now the problem arise at 2nd question, saying: Take the D.O.B from EMP_BRTH_REC and combine them to EMP_TBL_3:

I have tried this query:

ALTER TABLE EMP_TBL_3
ADD DOB int;

SELECT * FROM EMP_TBL_3;

SELECT EMP_ID, Name, DOJ, DOB
FROM EMP_TBL_3
UNION ALL
SELECT EMP_TBL_3.EMP_ID, EMP_TBL_3.Name, EMP_TBL_3.DOJ, EMP_BRTH_REC.DOB
FROM EMP_BRTH_REC 
LEFT JOIN EMP_TBL_3 ON EMP_BRTH_REC.EMP_ID = EMP_TBL_3.EMP_ID;

Results:

My desired output is:

How do I do that?

I've removed the RDBMS tags - please add back the one of interest. — Dale K
– Dale K, Commented Feb 19, 2020 at 4:29

zip · Accepted Answer · 2020-02-19 04:22:41Z

1

If i undertand propery, you just need to do this:

select EMP_TBL_3.EMP_ID, EMP_TBL_3.Name, EMP_TBL_3.DOJ, EMP_BRTH_REC.DOB
from EMP_BRTH_REC LEFT JOIN
     EMP_TBL_3
     on EMP_BRTH_REC.EMP_ID = EMP_TBL_3.EMP_ID;

answered Feb 19, 2020 at 4:22

zip

4,0612 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Venkataraman R · Accepted Answer · 2020-02-19 04:29:17Z

0

You are twice inserting the records using UNION ALL. I would suggest you to truncate and reload data into Tbl3 as given below:

ALTER TABLE EMP_TBL_3
ADD DOB int;

TRUNCATE TABLE Emp_Tbl_3;

INSERT INTO Emp_Tbl_3(Emp_Id, Name, DOJ, DOB)
SELECT EMP_TBL_1.EMP_ID, EMP_TBL_1.Name, EMP_TBL_1.DOJ, EMP_BRTH_REC.DOB
from 
EMP_BRTH_REC 
LEFT JOIN EMP_TBL_1
on EMP_BRTH_REC.EMP_ID = EMP_TBL_1.EMP_ID
UNION ALL
SELECT EMP_TBL_2.EMP_ID, EMP_TBL_2.Name, EMP_TBL_2.DOJ, EMP_BRTH_REC.DOB
from 
EMP_BRTH_REC 
LEFT JOIN EMP_TBL_2
on EMP_BRTH_REC.EMP_ID = EMP_TBL_2.EMP_ID;

answered Feb 19, 2020 at 4:29

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

1 Comment

Thirumalai vasan Over a year ago

This is right, but am getting additional 8 rows instead! i need only 8 rows

Collectives™ on Stack Overflow

Append the specific column in another existing table using SQL QUERY

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related