combining two identical tables( actually table fields) into one

this is actually a followup from a previous question but it contains different information , so I decided to start a new topic. Summary :

I have 2 tables one TMP_TABLE and a BKP_TABLE. Both have the same fieldstructure and identical datatypes for the fields( with almost identical data).

let's say TMP_TABLE is constructed like this

TMP_TABLE

NAME      PREFIX      PARAMETERS 
data 开发者_运维知识库     data        data
data      data        data
data      data        data

and BKP_TABLE looks like this

BKP_TABLE

NAME      PREFIX      PARAMETERS 
data1      data1        data1
data1      data1        data1
data1      data1        data1

Is it possible to combine these two tables into one that looks like this

END_RESULTTABLE 

   
    NAME      PREFIX      PARAMETERS 
    data      data1        data1
    data      data1        data1
    data      data1        data1

As you can see I wish to drop one of the fields and replace it with another. The sequence is pretty much the same so I don't have to worry about records being incorrect.

A side question

At the moment both TMP and BKP contain the exact same data ( 113 records) when I do this

SELECT * FROM TMP_TABLE 
UNION ALL 
SELECT * FROM BKP_TABLE 

I get 226. Why does this happen. I thought that duplicate entries ( which I can clearly see) would not appear in my virtual table.

EDIT:

I would like to replace one field of TMP_data with BKP_table field ( example like name).

UNION ALL  

will return all records from both selects (hence the ALL)

UNION

will remove duplicates

Assuming your two tables have a key in common (e.g. name), you can do something like this:

create table end_resulttable as
select t.name, t.prefix, b.parameters
from   tmp_table t, bkp_table b
where  t.name = b.name;

Is that what you mean?