Migration Millions of Records

Hi

Please guide me for the below given problem.

While doing migration by using cursors for the below given sample data its taking more hours to complete the process. Therefore want to know is there any way I can do it in simple query.

ACNo Amount Balance CalType
A001 10 10 +
A001 10 20 -
A001 40 40 +
A001 10 30 -
A002 90 90 +
A002 20 110 +
A002 40 150 +
A003 10 30 +
A003 10 40 +
A003 10 30 -
A004 40 40 +
A004 10 30 -

Iam having Amount value alone and Balance has to be calculated value based on CalType. At the same time the Balance has to be reset as 0 when AcNo has changed.

Please guide me for the faster approach.

Regards,
Mohanraj

It is not clear what you want help with.

Your data does not appear to be consistant. (It appears that the second row for [A001] should have a Balance of [0], and it appears that the first two rows of [A003] have incorrect Balance amounts.

Also, there is no column that can be used to determine sequencing, i.e., a datetime column or a IDENTITY field.

Do you wish ONLY a summary row with the correct Balance?

OR

Do you wish to 're-calculate' the Balance Column?

This can be very efficiently accomplished without using a CURSOR, #Temp tables, or table variables. It is really just a SET based operation.

|||

The data doesn't seem right.&nbsp; That said:&nbsp; there are ways to calculate running balances without using a cursor, but the methods (using COALESCE or cross-joins) are actually slower than using a cursor.&nbsp; (See <a href="http://www.sqlteam.com/article/calculating-running-totals">this article by Garth Wells</a> for a discussion of the various methods.)

What you don't want to do, though, is use a cursor to update your production table. Create a table variable to hold the running balances. Use a read-only cursor to read through your production table and populate the table variable. Then update the production table with the table variable when you're done.

|||

Hi Arnie,

Thanks for your reply.

ACNo Amount Balance CalType Ident_Column
A001 10 10 + 1
A001 10 20 - 2
A001 40 40 + 3
A001 10 30 - 4
A002 90 90 + 5
A002 20 110 + 6
A002 40 150 + 7
A003 30 30 + 8
A003 10 40 + 9
A003 10 30 - 10
A004 40 40 + 11
A004 10 30 - 12

Yeah, you are correct earlier data was wrongly posted, now it has highlighed as yellow in color.

Now I have added Identity column.

When AccountNumber has changed the balance need to recalculate.

Please provide me some faster approach.

Regards,

Mohanraj