vendredi 17 juin 2016

Create INSERT script with incrementing ID for linked tables (SQL server)

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