I need some help regarding an issue I'm having.
I need to migrate data from one database (ex. database1) to another database (ex. database2).
Both have the same structure but they're not in the same server and there's no communication between the servers, ultimately according to the client I can have a backup of the database1 to be restored on the server where the database2 is installed.
The problem is that I have a bunch of tables with identity seeded columns which their data need to be exported to the database2, and I've maintain the relationships between the main tables. My problem is that the database2 has also identity ids which are the same but their data is different. here's an example:
Database1
table_customer
id = 1
name = John Wood
...
table_workplace
id = 20
name = Europe
table_relation
id_customer = 1
id_worlplace = 20
Database2
table_customer
id = 1
name = Eric Bloomfield
...
table_relation
id_customer = 1
id_worlplace = 20
table_workplace
id = 20
name = Asia
I need to build a script to import these tables against the database2, is there a simple way to import all data and prevent data loss or broken relationships?
Many thanks and kind regards
You could turn IDENTITY_INSERT to ON on your destination tables, then inserting the records from the source increasing ID by a certain amount.
Suppose we have a table, named Test01, with three records, and a table named Test02 having the same structure and different data (except ID, which is the same on both tables). Wanting to add the data of Test02 to Test01, we can set IDENTITY_INSERT to ON on Test01, proceeding in copying the records increasing the ID from Test02.
In code, a thing like that:
CREATE TABLE Test01 (Id INT IDENTITY(1,1), SomeText VARCHAR(50))
CREATE TABLE Test02 (Id INT IDENTITY(1,1), SomeText VARCHAR(50))
INSERT INTO Test01(SomeText) VALUES('001'), ('002'), ('003')
INSERT INTO Test02(SomeText) VALUES('A'), ('B'), ('C')
SELECT * FROM Test01
SELECT * FROM Test02
GO
SET IDENTITY_INSERT Test01 ON
GO
INSERT INTO Test01(Id, SomeText) SELECT Id + 10000, SomeText FROM Test02
GO
SET IDENTITY_INSERT Test01 OFF
GO
SELECT * FROM Test01
SELECT * FROM Test02
DROP TABLE Test01
DROP TABLE Test02
GO
Executing the script will result in those steps:
Obviously, the ID increase must be done on all related tables, specifying the same amount for each. For situations with a great number of tables that approach can be uncomfortable, though.
Hope this helps.
