We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.)
Both databases are identical with many foreign-key constraints on tables.
Within a transaction we insert rows to various tables. The whole transaction will leave the tables in a state where all constraints are fulfilled.
But it may very well be, that in an intermediate state of the transaction a row gets inserted with a FK of another table that has not been inserted yet.
Is there a way to let SQL Server accept this and only check for violations once the whole transaction is committed?
Thanks for your help!