Need help for Migration Script using SQL Server

Posted by Bandi under Sql Server on 6/16/2014 | Points: 10 | Views : 402 | Status : [Member] [MVP] | Replies : 3
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



Responses

Posted by: Vuyiswamb on: 7/6/2014 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Why dont you drop the Constraints and Move the data and after that do a Data clean up and after that Create the Constraint again.

Thank you for posting at Dotnetfunda
[Administrator]

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/7/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Thank you for the given suggestion.

We doesn't wish to have FK violated data in the new Environment...
So am looping thru the failed batch row-by-row in the CATCH Block to find out the error-causing record...

Hence am able to migrate the consistent data to new system/app..

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

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 7/7/2014 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
HI Bandi

now when you do Data Migration it is always advisable to do data clean up before you migrate. so in your planning you missed the most important part. So i suggest you do data clean first and them migrate.

Thank you for posting at Dotnetfunda
[Administrator]

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response