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?

  • Have you tried by adding a batch seperator - GO after select .. MyTable1; GO select ....MyTable2;. – Kin Shah May 23 '15 at 0:38
  • 1
    You could add a where clause on the second query to ensure you only pull rows with a key that exists in your newly-created local copy of table1. – Aaron Bertrand May 23 '15 at 1:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.