bcp out and bcp in don't give the same number of rows

Hi,
I'm trying to export and import one big table in sql
server 2000 using bcp. Original table has 7,416,000 rows,
and after I bcp out this table into file in native format,
and bcp in from this file, also in native format, table
gets 7,478,000 rows and the data takes significantly less
space than before!
This table has char, varchar, numeric, datetime, and text
data types.
Can anybody tell me why the difference?
Thanks,
OJ
Hi,
Significatly less space:-
This is because your source table is fragmented heavily. The fragmentation
can be viwed by
DBCC SHOWCONTIG (Reger books online for info) command. By using this command
see the scan density, if the scan density is
too low than execute a DBCC DBREINDEX (Refer books online for usage and
info) command to remove the fragmention.
Cause for the fragmentation is because of the DML commands (Insert, Update
and Delete).
Row Difference:-
This may be bause of the inconsistency in sysindexes table. Execute the
below command to correct the inconsistency
and give the correct rows in the table. (Use the source table_name)
USE <dbname>
go
sp_spaceused <table_name>,@.updateusage = 'TRUE'
Thanks
Hari
MCDBA
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
> Hi,
> I'm trying to export and import one big table in sql
> server 2000 using bcp. Original table has 7,416,000 rows,
> and after I bcp out this table into file in native format,
> and bcp in from this file, also in native format, table
> gets 7,478,000 rows and the data takes significantly less
> space than before!
> This table has char, varchar, numeric, datetime, and text
> data types.
> Can anybody tell me why the difference?
> Thanks,
> OJ
|||Table is not fragmented at all. It is rebuilt before bcp
out. Statistics are also updated because of rebuilding the
table (clustered index with fillfactor 100)...
Thanks anyway,
OJ
>--Original Message--
>Hi,
>Significatly less space:-
>--
>This is because your source table is fragmented heavily.
The fragmentation
>can be viwed by
>DBCC SHOWCONTIG (Reger books online for info) command. By
using this command
>see the scan density, if the scan density is
>too low than execute a DBCC DBREINDEX (Refer books online
for usage and
>info) command to remove the fragmention.
>Cause for the fragmentation is because of the DML
commands (Insert, Update
>and Delete).
>Row Difference:-
>--
>This may be bause of the inconsistency in sysindexes
table. Execute the
>below command to correct the inconsistency
>and give the correct rows in the table. (Use the source
table_name)
>USE <dbname>
>go
>sp_spaceused <table_name>,@.updateusage = 'TRUE'
>--
>Thanks
>Hari
>MCDBA
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
rows,[vbcol=seagreen]
format,[vbcol=seagreen]
less[vbcol=seagreen]
text
>
>.
>
|||OJ (anonymous@.discussions.microsoft.com) writes:
> Table is not fragmented at all. It is rebuilt before bcp
> out. Statistics are also updated because of rebuilding the
> table (clustered index with fillfactor 100)...
How was it rebuilt? I've seen that when I have run DBCC DBREINDEX, there
has been a lot of extra space around, as it to have room next time
DBREINDEX is run.
How did you conclude that the number of rows were different? Did you
actually run a SELECT COUNT(*)? Or did you look at sysindexes.rows?
To get accurate values in this column - and in the space column,
run DBCC UPDATEUSAGE on the table.
If you indeed get different results with SELECT COUNT(*) before and
after running BCP, something is fishy. One alternative is that there
was some other data in the target table before you started. Also
check that the schemas of the tables are entirely identical.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp