SQL - query 2 databases ???

Nalz

Hi all

I'm looking to query 2 tables which are in different databases. Basically what I want to do is I have 2 databases, one being the newer version of the other. It consists of thousands of people with an unique id number. In the newer version, some fields got replaced with different student names.

i.e

Student ID = 201
Stud Name = "Joe Doe"

is now down in the new database as

Student ID = 201
Stud Name = "Slick Mick"

What is the easiest approach to retrieving a list of the new names and their relevant id fields using sql ?




thanks

bonkey

Depends on what you use to connect to the two databases.

One method would be to use MS Access, and Link tables from both databases. Then you can treat the resultant Access DB as one database.

Other systems offer similar functionality.

Until you decide how to connect to both sets of data, or at least what you're options are in this area, its hard to tell you the *easiest* way of doing it.

In SQL, once you ahve both sets, a JOIN will do what you need. Whether its an INNER, FULL OUTER, or something else...that will depend on what it is you need in the details.

bonkey

Alternately, if the two databases are on the same server, a la MSSQL, then you can reference both at the same time using fully-qualified (databasename.owner.objecname) names. And again, after that its just a join.

Nalz

probably use sql enterprise manager. so there is no exact syntax i can use to query 2 DBs at same time?

EDIT: question answered above... thanks

amen

if u have SQL enterprise manager the you can open a query window and do waht bonkey suggested

also in SQL Ent Manager you can another DB instance as a ref can't remember the command though
try Books On Line

FSL

If You are using enterprise manager the following query will work

SELECT {list of fields}
FROM {Second Database Name}.dbo.{Table Name} {Alias Name}
WHERE ({Id field Name} <>
(SELECT {Id field Name}
FROM {First Database Name}.dbo.{Table Name}
WHERE {Id field Name} = {Alias Name}.{Id field Name}))

Sorry made an error converting actual fields to generic names (I tested it on a database and a copy of the database with the compare field changed in several), it should read

SELECT {list of fields}
FROM {Second Database Name}.dbo.{Table Name} {Alias Name}
WHERE ({Compare field Name} <>
(SELECT {compare field Name}
FROM {First Database Name}.dbo.{Table Name}
WHERE {Id field Name} = {Alias Name}.{Id field Name}))

evilhomer

Just as another approach, if you don't require the two databases seperate for any particular reason, you could always DTS in all the table to the same db and append old_ in front of the old tables.

That way if you are using the data all the time you don't have to go doing cross DB joins.

So you would have syntax like this;

select * from student_info si 
left outer join old_student_info osi 
   on osi.student_id = si.student_id
   and osi.stud_name <> isnull(si.stud_name, '')

Just another thought, although if you need them to be separate databases, I have just wasted your time

amen

hmm I read the two databases as meaning
Server A database X

Server B database Y

not jsut database X and database Y on Server A

which is it?

Nalz

got it working with sql query below. Same server, different DBs

SELECT     MyDB_New.dbo.CardInfoTable.CardNumber As OldNumber,MyDB.dbo.CardInfoTable.CardNumber As NewNumber, 
                            FROM          MyDB.dbo.CardInfoTable, MyDB_New.dbo.CardInfoTable, 								

				MyDB.dbo.CardHolderTable, MyDB_New.dbo.CardHolderTable
                            WHERE      MyDB.dbo.CardInfoTable.CardID = MyDB.dbo.CardHolderTable.CardID AND
					MyDB_New.dbo.CardInfoTable.CardID = MyDB_New.dbo.CardHolderTable.CardID AND
					MyDB_New.dbo.CardInfoTable.CardNumber = MyDB.dbo.CardInfoTable.CardNumber AND
					MyDB_New.dbo.CardHolderTable.EmployeeNumber <> MyDB.dbo.CardHolderTable.EmployeeNumber 

democrates

Assuming this is a migration rather than two db's needing to run in parallel:

If unique ID's are based on an auto-increment field the trick may be to define the new tables without auto-inc set, insert from the old db, then set the field to auto-inc. That should keep the relationship between id and the rest of the record intact.

Warning: My recollection of MS DB quirks is hazy so a quick test is indicated...

evilhomer

democrates wrote:

Assuming this is a migration rather than two db's needing to run in parallel:

If unique ID's are based on an auto-increment field the trick may be to define the new tables without auto-inc set, insert from the old db, then set the field to auto-inc. That should keep the relationship between id and the rest of the record intact.

Warning: My recollection of MS DB quirks is hazy so a quick test is indicated...

In this case you would set the table up as normal (with the auto_increment on)

and insert using

SET IDENTITY_INSERT new_table OFF -- allows you to insert into the identity column

insert into new_table
(
id
,card_number
)
select
old_id
,card_number
from old_table

SET IDENTITY_INSERT new_table ON -- reset to ON so auto increment occurs in future.