Merge two exact tables

[pcusters]

I have two tables with the same structure and data.
I want to merge this. I use the next query:

insert into CUSTOMER_A
select *
from CUSTOMER_A_TEMP

He gave an error with the primary key. The are the same.
How can I make it happen that these will be differend?

Pieter

[Vidhura]

Merge this into a seperate table or the one of the two tables?

[Motoma]

I have two tables with the same structure and data.
I want to merge this. I use the next query:

insert into CUSTOMER_A
select *
from CUSTOMER_A_TEMP

He gave an error with the primary key. The are the same.
How can I make it happen that these will be differend?

Pieter

You will not be able to insert your primary key.

INSERT INTO CUSTOMER_A (Row2, Row3, Row4...all rows except the PK)
(SELECT Row2, Row3, Row4...the same rows as above FROM CUSTOMER_A_TEMP)
 

[siva538]

You will not be able to insert your primary key.

INSERT INTO CUSTOMER_A (Row2, Row3, Row4...all rows except the PK)
(SELECT Row2, Row3, Row4...the same rows as above FROM CUSTOMER_A_TEMP)
 

This will work only if the pk column is identity in customer_A table.

[Motoma]

This will work only if the pk column is identity in customer_A table.

Good eye, you are correct with that statement.

[nomad]

This will work only if the pk column is identity in customer_A table.

but they should be able to add a new field (ie PK) once the new table is set and go from there if the db is not to big.

[siva538]

but they should be able to add a new field (ie PK) once the new table is set and go from there if the db is not to big.

I dont' think a new table is required in this situation. If the following steps are used sql is smart enough to take care of temp table creation during altering with identity
1. Make sure that destination table has a primary key (automatically uniqueness is taken care)
2. update destination table with the data from customer_a_temp where key matches (if any records exist with this kind)
3. Alter this column (if not) to identity for ensuring future data from customer_temp
4. finally use the above insert stmt for transferring data where keys does not exists

let me know if you have still have any questions.

Thanks,
Sivakumar.