I have a large table having millions of rows. There is one datetime column which captures the date and time a row is inserted to the table. Now, I want to copy the table's data into another server's table with same structure. Here the client wants me to migrate the data in a page by page manner (say 100 rows at a time). This table does not have any identity or unique key. I am thinking of using the row_number () function on the datetime column. But I have one doubt... say the trasaction fails somewhere in between... say at row number 115... it is written like the process will start again from 101 and not from 1. But is there any guarantee that the row_numbers will be same considering the datetime field can have duplicate values?

snigdhandream (6/19/2012)I have a large table having millions of rows. There is one datetime column which captures the date and time a row is inserted to the table. Now, I want to copy the table's data into another server's table with same structure. Here the client wants me to migrate the data in a page by page manner (say 100 rows at a time). This table does not have any identity or unique key. I am thinking of using the row_number () function on the datetime column. But I have one doubt... say the trasaction fails somewhere in between... say at row number 115... it is written like the process will start again from 101 and not from 1. But is there any guarantee that the row_numbers will be same considering the datetime field can have duplicate values?

Regards,Snigdha

Is there a combination of columns which can together form a unique key? Or to put it another way, are there any row-wise exact dupes in the table? If you can find a combination of columns which form a unique key, then something like the following would work:

Going along with the previous response, if you cannot come up with a unique constraint (single or multiple columns) then it really doesn't matter if duplicates are moved or not EDIT(in many cases, not all). If there is no way to tie back to the data uniquely, then it may be irrelevant. It sounds like you may have a natural unique key on your datetime column (assuming it is datetime and not date). Have you tried doing a distinct count on some of the columns?

Thank you guys... This time I pressurized the client to consider adding unique keys to the tables those don't have one already. And I think they are considering, because they asked me to look into the DB and list out all the tables not having a unique key :P. In this case, row_number() will work :)