Need help for Migration Script using SQL Server

Hi All,

I need some kind of suggestion to resolve the below issue,

we are migrating data from one database (for instance DB1) to another(DB2), each of these databases are in same server. DB1 is not having Foreign key whereas DB2 is having Foreign keys(relationships). And we have millions of records in DB1 tables.

I would like to find out the FK violated records while dumping DB1 tables data to DB2 tables and then move those records to some other table....

i = (Count of records) / 10
minRow=1, MaxRow=10
While ( i > 0)
{
   --dumping records to DB2 table, For example
    insert DB2.dbo.Table1 
    SELECT * FROM DB1.dbo.Table1 where row_number between minRow and maxRow;

    SET minRow =  maxRow+1;
    SET MaxRow = MaxRow+10;
    i = i-1;
}

Like the above code am looping through each of 10 records at a time. If any FK violation happens then it is rolling back all 10 records due to one record vioaltion. Here, i would like to catch only the failure causing record and all remaining records to be committed.

Kindly help me to achieve this.

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif