How to retrieve different records from same table present in different databases

Question

How to retrieve different records from same table present in different databases

sagar-506255

SSC Veteran

Points: 285
More actions
November 14, 2007 at 9:37 pm

#62192

Hi guys,
I hv same table say "Patient" in 2 different databases that are present on different or same server, Now i want to get those different records from the "Patient" table that are present in these databases.
Does anyone have any stored procedure or a SQL query that will solve my above problem.
I need it urgently.......
thanks,
Sagar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

What exactly are you looking for? Do you want to select rows that are different in the 2 tables? Are the databases on the same server or not?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sagar-506255 SSC Veteran Points: 285 More actions · Answer 2

Yes, i want to select rows that are different in the 2 tables which are of same table "patient" bt present in different databases.

the databases are present on different servers.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

Create a linked server and query the two like you would normally. A linked server requires a 4 part name though.

Server.Database.Table.Column

My blog: http://jahaines.blogspot.com

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Yes, just as Adam said, create a linked server and then run the query using the 4 part names. As for the query, you are looking for rows that exist in database1 but not in database 2 and vice versa correct? If so, a FULL OUTER JOIN will work for you. Here's a basic example returning only rows that don't have a match either way.

declare @table1 table (Col1 int)

insert into @table1

select 1 union all

select 2 union all

select 3 union all

select 5 union all

select 7 union all

select 9

declare @table2 table (Col1 int)

insert into @table2

select 1 union all

select 2 union all

select 4 union all

select 6 union all

select 8 union all

select 10

SELECT t1.col1, t2.col1

FROM @table1 t1

full join @table2 t2

on t1.col1 = t2.col1

WHERE t1.Col1 IS NULL OR t2.Col1 IS NULL

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SHAIKH, Mohammad Imran Grasshopper Points: 15 More actions · Answer 5

Hi there;

If you can help as well for similar kind of situation i.e.:

I have 3 databases (AD_DEPT, SC_DEPT & LAW_DEPT) residing same server with 3columns same column names as (FirstName, LASTNAME, DEPTNAME)

AD_DEPT posses all the users with their dept

SC_DEPT posses all SC's users with their dept

LAW_DEPT posses all LAW's users with thier dept

Originally, these all users should be exisitng with the same name & their dept in all 3 system at the same time but due to big mess the users match in all 3databases somehow but their dept are screwed and given different name in each database

I want to retireve all users like:

DISCREPANCIES BETWEEN SYSTEMS

Name Department

LWS AD SC

ADRIANNE_SEQUEIRA WITHDRAWL MWU/Addictionsnull

MANAGEMENT SERVICES

help would much appreciated!