Data Export of SQL Server table from one server to another / Data Flow task error

Question

I am trying to export the data from pre-prod to prod server but somehow it is failing.

Right Click-> Task -> Export -> Source -Destination, choose table

Dropping and recreating of the table is not possible on production I believe because it has already 300,000 data records on prod.

How can I amend the records from pre-prod (600,000 records) to prod?

Any help greatly appreciated

Error I got

Answer 1

In the Edit Mappings page when Select Source Tables and Views before you hit the finish button , check Append rows to the destination table and check Enable identity insert if you have: 1.identity column in your destination table ; 2.Would like to append the 600K rows not overwrite the original 300K; Otherwise you have to take care of the source data to see whether it violates the target table schema, such as NULL is accepted or not and so on.

Answer 2

Workaround for this situation would be to

select * into preprodtable_bak from preprodtable 

Then clean preprodtable_bak from any records that already exist in prod table(using range of id's maybe) or any duplicates.

Then export data from the preprodtable_bak table.