Sql-server – Combine two table with different column but same value

sql serversql-server-2008

I have two tables with similar value:

TABLE A

a_empno  a_checktime               a_updatetime            a_status  a_location
-------  -----------------------   ----------------------- -------   ---------
1        2016-03-30 07:40:07.000   2016-03-30 07:42:50.843  In        xxxxxxx
2        2016-03-30 07:40:09.000   2016-03-30 07:42:51.543  In        xxxxxxx
3        2016-03-30 18:18:26.000   2016-03-30 19:14:09.673  Out       xxxxxxx
4        2016-03-30 18:18:30.000   2016-03-30 19:14:10.020  Out       xxxxxxx

TABLE B

b_empno  b_checktime               b_updatetime            b_status  b_location
-------  -----------------------   ----------------------- -------   ---------
1        2016-03-30 07:40:07.000  2016-03-30 08:01:01.000   In        xxxxxxx
2        2016-03-30 18:18:26.000  2016-03-30 21:01:01.370   In        xxxxxxx

I'm trying to combine the table so I can get a result where the data in table B will be combined into table A:

a_empno  a_checktime               a_updatetime            a_status  a_location
-------  -----------------------   ----------------------- -------   ---------
1        2016-03-30 07:40:07.000   2016-03-30 07:42:50.843  In        xxxxxxx
2        2016-03-30 07:40:09.000   2016-03-30 07:42:51.543  In        xxxxxxx
3        2016-03-30 18:18:26.000   2016-03-30 19:14:09.673  Out       xxxxxxx
4        2016-03-30 18:18:30.000   2016-03-30 19:14:10.020  Out       xxxxxxx
1        2016-03-30 07:40:07.000  2016-03-30 08:01:01.000   In        xxxxxxx
2        2016-03-30 18:18:26.000  2016-03-30 21:01:01.370   In        xxxxxxx

Do I have to create another table or is there any other way to do this? I have tried EXISTS and JOIN.

Best Answer

You should be able to use a simple INSERT/SELECT FROM.

INSERT INTO TABLE_A (
    a_empno
    ,a_checktime
    ,a_updatetime
    ,a_status
    ,a_location
    )
SELECT b_empno
    ,b_checktime
    ,b_updatetime
    ,b_status
    ,b_location
FROM TABLE_B

If your goal is to combine the results from the two tables into another table, you should do as McNets suggests in his comments and use either UNION or UNION ALL.

SELECT a_empno
    ,a_checktime
    ,a_updatetime
    ,a_status
    ,a_location
FROM TABLE_A
UNION   --OR UNION ALL IF YOU WANT TO KEEP DUPLICATES
SELECT b_empno
    ,b_checktime
    ,b_updatetime
    ,b_status
    ,b_location
FROM TABLE_B
Related Question