Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
BenHi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
David Portas
SQL Server MVP
--

Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
Ben
Hi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>
|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
David Portas
SQL Server MVP

Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
BenHi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
--
David Portas
SQL Server MVP
--

Compare DataBases......

Hi

I want Tom Compare two databases to see if there are stroed procedure \ table that exist in one database

but not exist in the other.....

how do i do it?

Thanks

hi,

Please find the solution below:

Code Snippet

--SQL 2005

select [name] from <database name>..sysobjects

where type = 'P'

except

select [name] from <database name>..sysobjects

where type = 'P'

--SQL 2000

select [name] from <database name>..sysobjects

where type = 'P'

and [name] not in (

select [name] from <database name>..sysobjects

where type = 'P')

|||

There are several good third party products that will compare databases, and provide you with scripts to make them the same (as well as reports). These products will not only compare the object names, but will also compare the object definitions to see if they are the same.

I suggest that you explore Red Gate's SQL Compare, or ApexSQL's SQL Diff products. Visual Studio for Database Professionals also has that capability. (Most have a 14-30 day fully featured evaluation version so that you can try them out.)

Comparison Tools
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
RAC4SQL's QALite (Free)
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Data Comparison
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
TASC - SQL Delta
TulsaSoft - SQL Data Examiner
xSQL Software - xSQL DataCompare
DTS Comparison
Red Gate – DTS Package Compare
Server Comparison
Quest - ServerCompare
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition

|||

Lots of ways to do this as mentioned above. Also, the Visual Studio Team Edition for Database Professionals has this built in. you can also code your own:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=108&rl=1

Compare databases between 2 SQL servers

Hello,
I wish to see if the tables from database A server A1 exist in database
A on server B1.
I setup a linked server from my first server (robertcamarda) to a
target (cognos-dev)
This works from robertcamarda:
select count(*) from [cognos-dev].ds_v6_source.dbo.stdmas

and this works:
select * from ds_v6_source.information_schema.tables order by
table_name

but this wont work:
select * from [cognos-dev].ds_v6_source.information_schema.tables order
by table_name

Error:
Msg 117, Level 15, State 1, Line 1
The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
contains more than the maximum number of prefixes. The maximum is 3.

I want to do something like:
select * from ds_v6_source.information_schema.tables
where table_name not in (select table_name from
[cognos-dev].ds_v6_source.information_schema.tables order by
table_name)

so I can see of the sql server (robertcamarda) has any missing tables
that exist on the server (cognos-dev)
TIA
Rob
SQL Server 2005 Enterpriseuse sqlcompare tool to compare anything and everything between two
databases

www.red-gate.com

download the trial version and see if it works for you.|||On 17 May 2006 05:30:36 -0700, "rcamarda" <robc390@.hotmail.com> wrote:

>Hello,
>I wish to see if the tables from database A server A1 exist in database
>A on server B1.
winsql has a trial that is pretty useful
hth
Jeff Kish|||rcamarda (robc390@.hotmail.com) writes:
> but this wont work:
> select * from [cognos-dev].ds_v6_source.information_schema.tables order
> by table_name
> Error:
> Msg 117, Level 15, State 1, Line 1
> The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
> contains more than the maximum number of prefixes. The maximum is 3.

There is an apparent inconsistency between the error message and the
command you presented.

Then again, I was not able to get it to work with the proper command
either. This is probably because the INFORMATION_SCHEMA views are
not present in the databases, only in master. (Hm, I believe they
were in SQL 7, so with if you connect to SQL 7 it might work.)

Rather than using INFORMATION_SCHEMA, use the system tables or
the catalog views depending on which version of SQL Server you
are on.

SELECT name FROM [cognos_dev].ds_v6_source.dbo.sysobjects
WHERE type = 'U'
ORDER BY name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

compare all the records of all the fields of two tables

Does some exist it sorts things out of to compare all the records of all the
fields of two tables and to see that this different one among the tables?
Each table has 90 fields
example
table 1 table 2 table1
table2
field1=a field1 = b field2=5 field2=6
field1=u field1=k field2=9 field2=8
field2=10
field2=50Red Gate has a very popular tool to do this..(if you don;t wish to write
your own).
You may also find a script on www.sqlservercentral.com
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:O8KD8ogjDHA.2232@.TK2MSFTNGP09.phx.gbl...
> Does some exist it sorts things out of to compare all the records of all
the
> fields of two tables and to see that this different one among the tables?
> Each table has 90 fields
>
> example
> table 1 table 2 table1
> table2
> field1=a field1 = b field2=5
field2=6
> field1=u field1=k field2=9
field2=8
> field2=10
> field2=50
>
>
>

compacting / to repair base of data sql, using the msde

I am developing a project of the access for a friend, more I am with the
following doubts:
Does some exist it sorts things out of compacting / to repair base of data
sql, using the msde or the adp.?
MSDE databases do not require compacting. When updates or other changes are
made, the space freed when a row is deleted is reused automatically. For the
most part, MSDE/SQL Server databases are not subject to the file corruption
problems that are associated (from time-to-time ) with JET databases. There
are utilities (DBCC for one) that can be used to perform maintenance on the
database, but it's unusual to have to resort to these for smaller databases
as typically implemented with MSDE.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:%23ZUa0OKMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am developing a project of the access for a friend, more I am with the
> following doubts:
> Does some exist it sorts things out of compacting / to repair base of data
> sql, using the msde or the adp.?
>