Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!
Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL...pare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegr oups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>
|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQ...mpare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL_Data_Compare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.