I have a regular task which involves exporting two tables from one database and importing the data into another database whose corresponding tables are not empty. One of the tables in question has a column which refers to the ID of the first. Let's call them Customer and Customer_Address where Customer_Address has a column Cust_ID referring to ID in the Customer table.
I need to create an import script for these which will add the Customer records to the other, non-empty DB with ID = max(id)+1 for each row while not breaking the link to the other table. The Customer_Address table likewise has its own ID column which needs to be incremented in the same manner.
I don't strictly need to have the first inserted record be one higher than the existing highest ID, but it would be best. I've managed in the past to manually check the highest ID in the target DB and add this number using search and replace and a variable in the import script but it's laborious and fails if any records are added to the target DB in the interim.
I have another method which involves selecting, copying, pasting and SQL-wrapping selected all columns but ID from the source tables using Excel and using select(max) instead of the ID but again it's rather tedious.
Edit with data: Sample script to create source and destination tables (in the same/temdb database for convenience) is here: http://pastebin.com/C64wFtsP
Should give output as follows:
select * from customer
id Last First 1 Johnson James 2 Kelly Karl 3 Lawlor Liam
select * from customer2
id Last First 1 Adams Ann 2 Byrne Bressie 3 Casey Charlene
select * from customeraddress
id idclient street city county country 1 1 65 North St. Marcoussin Jojoba Flatland 2 2 42 South St. Marcoussin Jojoba Flatland 3 3 12 West St. Marcoussin Jojoba Flatland 4 1 17 East St. Marcoussin Jojoba Flatland 5 1 75 Centre St. Marcoussin Jojoba Flatland
select * from customeraddress2
id idclient street city county country 1 1 99 North St. Marcoussin Jojoba Flatland 2 2 88 South St. Marcoussin Jojoba Flatland 3 3 88 West St. Marcoussin Jojoba Flatland 4 1 66 East St. Marcoussin Jojoba Flatland 5 1 55 Centre St. Marcoussin Jojoba Flatland
What I'm looking for is a way to script an import of the data in both source tables to the corresponding destination tables, while preserving the link between the idclient in customeraddress and the id in customer.
Aucun commentaire:
Enregistrer un commentaire