Table Copy Resulting in extra rows

Question

Table Copy Resulting in extra rows

Adam Kreul

Ten Centuries

Points: 1391
More actions
November 6, 2008 at 1:12 pm

#154897

Hi all,
I have an interesting situation occuring and hope someone out there can shed some light on the issue..I'll try to give as much background as possible.
We have a table in Development with 19,410,958 rows. This table was not originally developed by a DBA, and therefore is poorly designed. The primary key is currently made up 6 different columns. We proposed to add an identity field to the table and have that single field serve as the primary key.
In order to do this, I have written a script that creates a temporary table with the exact same structure as the original, the only difference being the new identity field. The data from the old table needs to be preserved. My script selects all the rows from the original table, inserts them into the temporary table, drops the original table, and then renames the temporary table to the original. This should be preserving the data, along with adding an identity field that auto populates when inserting the 19 million rows.
This appears to work, however the temporary table ends up with 19,412,323 rows rather than 19,410,958 rows. With this much data, it would be near impossible to track down the extra rows with the naked eye. Both tables should be identical, with the only exception being the temporary table has the new identity field. Does anyone know why there would be around 1,500 extra rows?
Thanks in advance,
Adam

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

Is the rowcount on the primary table consistent? Try to create the temp table without the identity key and see for the number of rows, I guess it will be same then

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Did you determine the count of 19,410,958 by using rowcount, or by "right click" -> "Properties" ?? The "properties" is not always accurate.

Adam Kreul Ten Centuries Points: 1391 More actions · Answer 3

Yes, I realized that. I did a select count(*) and the correct number was returned. Thanks for the response!

sqlizer SSCrazy Points: 2120 More actions · Answer 4

I don't know why you are getting difference but I tried it in AdventureWorks database on SalesOrderdetail table, the way you mentioned

(Like creating temp table, dumping data from original table to the temp table, dropping original table, and creating new table with all the columns of the original table and then dumping data back into the original table from the temp table) and it worked fine. You can try to use tablediff utility which is built in to the sql server and below is the link for GUI version of it.

http://weblogs.sqlteam.com/mladenp/archive/2007/08/10/60279.aspx

It's a free utility.

Hope this helps.

Adam Kreul Ten Centuries Points: 1391 More actions · Answer 5

I believe it was because when you go through the GUI, if the stats have not been updated recently, there will be a difference shown. This table was updated frequently, and the stats were not up to date. When I did a select count(*) on the original table, the row count matched up to the temporary.