Compare tables

can any one suggested me a better way to compare two tables.
I tried following,
create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
insert into t1(col1,col2) values('c11','c12')
insert into t1(col1,col2) values('c21','c22')
insert into t1(col1,col2) values('c31','c32')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t2(col1,col2) values('c11','c12')
insert into t2(col1,col2) values('c21','c22')
insert into t2(col1,col2) values('c31','c32')
insert into t2(col1,col2) values('c41','c42')
select col1 from
(
select col1,col2 from
t1
union all
select col1,col2 from
t2
) a
group by col1,col2 having count(*)<>2skg,
"What" is it that you want to find out?
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
> can any one suggested me a better way to compare two tables.
> I tried following,
> create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> insert into t1(col1,col2) values('c11','c12')
> insert into t1(col1,col2) values('c21','c22')
> insert into t1(col1,col2) values('c31','c32')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
>
> insert into t2(col1,col2) values('c11','c12')
> insert into t2(col1,col2) values('c21','c22')
> insert into t2(col1,col2) values('c31','c32')
> insert into t2(col1,col2) values('c41','c42')
>
> select col1 from
> (
> select col1,col2 from
> t1
> union all
> select col1,col2 from
> t2
> ) a
> group by col1,col2 having count(*)<>2
>|||Since you do not have any keys and the columns are NULL-able, this is
probably as good as anything else.|||Thanks!!. I want to to know if both the tables have same rows of data. i.e
both tables are same.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
> skg,
> "What" is it that you want to find out?
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||skg,
You can start with something like this:
SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
and work it with IS NOT NULL etc.. depending on your needs and requirements.
A more flexible and robust solution would be to use a third-party software
package to make the changes if required. See (as an example):
http://www.red-gate.com/products/SQ...mpare/index.htm
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
> Thanks!!. I want to to know if both the tables have same rows of data. i.e
> both tables are same.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||Thanks!!! Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
> skg,
> You can start with something like this:
> SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
> and work it with IS NOT NULL etc.. depending on your needs and
> requirements.
> A more flexible and robust solution would be to use a third-party software
> package to make the changes if required. See (as an example):
> http://www.red-gate.com/products/SQ...mpare/index.htm
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
>|||skg
SELECT a.col1,a.col2,b.col1,b.col2
From (Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t1 ) a
Inner Join (
Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t2 ) b
On a.col1 = b.col1 and a.col2 = b.col2
Where a.CheckSum = b.CheckSum
"skg" <skg@.yahoo.com> wrote in message
news:umyMn5dzFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks!!! Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
>

Compact method to reset Identity columns

I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.

Any help is greatly appreciated!

Kind Regards,

Mat

Try out: ALTER TABLE <IdentityTable> ALTER COLUMN <Identity Column> <Data Type> (51, 1);

Thanks,

Laxmi

|||

I tried this:

ALTER TABLE asset ALTER COLUMN iid bigint (51,1)

but get the following error message:

The specified data type is not valid. [ Data type (if known) = bigint ]

Bigint is the data type of this field, but I get the same error message if I try int as well. Thanks for your help!

Kind Regards,

Mat

|||

Use

"ALTER TABLE asset ALTER COLUMN iid IDENTITY (51,1)"

|||

Pragya,

This worked perfectly!

Thanks,

Mat

Compact method to reset Identity columns

I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.

Any help is greatly appreciated!

Kind Regards,

Mat

Try out: ALTER TABLE <IdentityTable> ALTER COLUMN <Identity Column> <Data Type> (51, 1);

Thanks,

Laxmi

|||

I tried this:

ALTER TABLE asset ALTER COLUMN iid bigint (51,1)

but get the following error message:

The specified data type is not valid. [ Data type (if known) = bigint ]

Bigint is the data type of this field, but I get the same error message if I try int as well. Thanks for your help!

Kind Regards,

Mat

|||

Use

"ALTER TABLE asset ALTER COLUMN iid IDENTITY (51,1)"

|||

Pragya,

This worked perfectly!

Thanks,

Mat

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.