I need to import 2 tables from a linked SQL server. So far, the following code has been successful:
SELECT * INTO MyTable1 FROM MyLinkedServer.MyDb.dbo.MyTable1; SELECT * INTO MyTable2 FROM MyLinkedServer.MyDb.dbo.MyTable2;
In MyLinkedServer, MyTable2 has a foreign key that references MyTable1's PK.
Once in a while a record is added to both tables before the second
SELECT INTO completes, so imported data losses referential integrity. In other words, MyTable2 references an entity (row) that doesn't exist in MyTable1.
How do I accomplish this with referential integrity if MyLinkedServer is not running the DTC (Distributed Transaction Coordinator) service?