Check FK Constraints after BULK INSERT

Question

I have a couple of big datasets ( ~O(1TB) ), which I want to to import into my database. I use BULK INSERT to import data into temporary tables, then use SELECT and INSERT INTO to fill my real tables with the data. This is because I want to change the order of some things, and split some data files into logical tables. And if this functionality is not needed, I just BULK INSERT directly into my target table.

I would like to check if all foreign key constraints have been enforced. If I flag BULK INSERT to CHECK_CONSTRAINTS during the import stage the import process slows down to a crawl.

Is there a command to do this after the fact? I have very limited familiarity with SQL Server, and databasing in general.

Thanks in advance.

EDIT:

Suggested reading : MSDN Article

Answer 1

Tibor Karaszi wrote a great article about trusted constraints: Non-trusted constraints

Answer 2

How many tables/foreign keys are you looking at? Have you considering writing a quick custom query that checks for orphaned rows?

Answer 3

To check one table:

alter table YourTable with check check constraint all

To check all tables:

exec sp_msforeachtable 'alter table ? with check check constraint all'