|
Home > Archive > MS SQL Server New Users > December 2005 > Compare tables in different database on the same server
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Compare tables in different database on the same server
|
|
|
| I am trying to compare 1 table in database Production_ED and 1 table on
database Sales_Force. My query is:
select production_ed.company.vista_account from
production_ed.company
where
sales_force.dbo. Salesforce_Duplicate
_Accounts.vista_account_id
!= production_ed.company.vista_account
I am using query analyzer and even tried a union but SQL Server 2000 SP4
requires you to select a database from the drop down menu when you prepare a
sql statement.
The field I need to check is production_ed.company.vista_account in 1 table
and sales_force. salesforce_duplicate
_accounts.vista_account_id in the other.
Any help will be appreciated.
--
Ray
| |
| Andrew J. Kelly 2005-12-01, 8:24 pm |
| select a.company.vista_account
from production_ed.company AS a
where NOT EXISTS (SELECT * FROM
sales_force.dbo. Salesforce_Duplicate
_Accounts AS b
WHERE b.vista_account_id = a.company.vista_account)
--
Andrew J. Kelly SQL MVP
"Ray" <ray.smith@loislaw.com> wrote in message
news:4EE3E863-85E2-4EB6-95DE- 2D3C6C105952@microso
ft.com...
>I am trying to compare 1 table in database Production_ED and 1 table on
> database Sales_Force. My query is:
> select production_ed.company.vista_account from
> production_ed.company
> where
> sales_force.dbo. Salesforce_Duplicate
_Accounts.vista_account_id
> != production_ed.company.vista_account
>
> I am using query analyzer and even tried a union but SQL Server 2000 SP4
> requires you to select a database from the drop down menu when you prepare
> a
> sql statement.
>
> The field I need to check is production_ed.company.vista_account in 1
> table
> and sales_force. salesforce_duplicate
_accounts.vista_account_id in the
> other.
>
> Any help will be appreciated.
> --
> Ray
| |
|
| Andrew,
Tried your code and got the same error message:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production_ED.Company'.
We have the right database, table, and column. In query analyzer it has you
select a table from the drop down menu . I tried Production_Ed, Sales_force,
master, etc., and get the same error message. I even tried to but dbo. in
front of company and got an error saying:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'a.company' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'a.company' does not match with a table name or
alias name used in the query.
Any ideas?
Ray
--
Ray
"Andrew J. Kelly" wrote:
> select a.company.vista_account
> from production_ed.company AS a
> where NOT EXISTS (SELECT * FROM
> sales_force.dbo. Salesforce_Duplicate
_Accounts AS b
> WHERE b.vista_account_id = a.company.vista_account)
>
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Ray" <ray.smith@loislaw.com> wrote in message
> news:4EE3E863-85E2-4EB6-95DE- 2D3C6C105952@microso
ft.com...
>
>
>
| |
| Andrew J. Kelly 2005-12-03, 3:23 am |
| Can you provide the DDL for the two tables?
--
Andrew J. Kelly SQL MVP
"Ray" <ray.smith@loislaw.com> wrote in message
news:DEB1ACC8-96B2-42E4-B2F2- 666F3A49F804@microso
ft.com...[color=darkred]
> Andrew,
>
> Tried your code and got the same error message:
>
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production_ED.Company'.
>
> We have the right database, table, and column. In query analyzer it has
> you
> select a table from the drop down menu . I tried Production_Ed,
> Sales_force,
> master, etc., and get the same error message. I even tried to but dbo. in
> front of company and got an error saying:
>
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'a.company' does not match with a table name or alias
> name used in the query.
> Server: Msg 107, Level 16, State 1, Line 1
> The column prefix 'a.company' does not match with a table name or
> alias name used in the query.
>
> Any ideas?
>
> Ray
>
>
> --
> Ray
>
>
> "Andrew J. Kelly" wrote:
>
| |
|
| Used this and it worked. Don't know how good the inner join is though.
SELECT a.vista__account, b.vista_account_id, count(*)as 'count'
FROM sales_force.dbo. Salesforce_Duplicate
_contacts b
INNER JOIN production_ed.dbo.contact a
ON a.vista__account = b.vista_account_id
WHERE b.vista_account_id <> a.vista__account
group by a.vista__account, b.vista_account_id
--
Ray
"Andrew J. Kelly" wrote:
> Can you provide the DDL for the two tables?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Ray" <ray.smith@loislaw.com> wrote in message
> news:DEB1ACC8-96B2-42E4-B2F2- 666F3A49F804@microso
ft.com...
>
>
>
|
|
|
|
|