Comparision of tables in two instances

Question

Comparision of tables in two instances

Minnu

SSCertifiable

Points: 6451
More actions
March 20, 2013 at 2:12 am

#271800

Am having two instances A and B, in that having a table called students in two databasess, but the records are not in match,
i want to retrieve records which are having in "A" database and not having in "B" database.
Instance : A
Database : Students
Table : Stud_Info
Instance : B
Database : Stud
Table : Stud_Info.

Viewing 15 posts - 1 through 15 (of 21 total)

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

You need join your tables with a left join. This will shows which records are in one table but not in the other.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Minnu

SSCertifiable

Points: 6451

March 20, 2013 at 2:37 am

#1598772

Hi Team,

Sample query Please........

Stuart Davies SSCoach Points: 19505 More actions · Answer 3

select cola1,cola2,colb1,colb2 from tablea left outer join tableb on cola1 = colb1 where colb1 is null

Sample query.

Two points:-

This could be more accurate if you could provide table structure (see first link in my signature).

You need to understand this if you want to enhance your skills this linkhttp://msdn.microsoft.com/en-gb/library/ms187518(v=sql.105).aspx provides more information. The same information is also in Books on line

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Am using below query, but not executing,

select * from Stud_Info

left inner join SVSINST\SR2002.students.stud_Info

Please help me in query building....

Stuart Davies SSCoach Points: 19505 More actions · Answer 5

You have an error in your query.

Please re-read my sample query and the link I posted - you might see the error then.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Minnu SSCertifiable Points: 6451 More actions · Answer 6

Query Looks great,

But i want to retrieve unmatched records from two seperate instances.

Instance : A

Database : Students

Table : Stud_Info

Instance : B

Database : Stud

Table : Stud_Info.

output should be displayed from stud_info tables from two databases/instances

Stuart Davies SSCoach Points: 19505 More actions · Answer 7

What is the query you are using at present?

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Minnu SSCertifiable Points: 6451 More actions · Answer 8

select * from Stud_Info

left inner join SVSINST\SR2002.students.stud_Info

Stuart Davies SSCoach Points: 19505 More actions · Answer 9

Minnu (3/20/2013)
select * from Stud_Info
left inner join SVSINST\SR2002.students.stud_Info

Left inner join ?

When I try to run left inner join I get :-

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'INNER'.

Please re-read my very basic example and the example in books on line.

I am sorry if it sounds like I am labouring the point but I won't be supporting you system - you will be - you need to understand this.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

tim_harkin SSCrazy Points: 2399 More actions · Answer 10

Minnu (3/20/2013)
Am having two instances A and B, in that having a table called students in two databasess, but the records are not in match,
i want to retrieve records which are having in "A" database and not having in "B" database.
Instance : A
Database : Students
Table : Stud_Info
Instance : B
Database : Stud
Table : Stud_Info.

With out actual table definitions, this is kinda a stab in the dark. Something along the lines of

SELECT * FROM TableA as A

WHERE A.ID not in (SELECT B.ID from TableB as B)

David McKinney SSChampion Points: 10472 More actions · Answer 11

....you also need a linked server to query the other instance.

If you run your query on InstanceA then you need to create a linked server to point to InstanceB, at which point I would do something like

Use stud

go

-- all in InstA which are not in InstB

select * FROM dbo.Table1

EXCEPT

select * FROM InstanceB.Stud.dbo.Table1

-- all in InstB which are not in InstA

select * FROM InstanceB.Stud.dbo.Table1

EXCEPT

select * FROM dbo.Table1

If you don't know what a linked server is....search.

Horatiu SSChasing Mays Points: 652 More actions · Answer 12

Minnu (3/20/2013)
Am having two instances A and B, in that having a table called students in two databasess, but the records are not in match,
i want to retrieve records which are having in "A" database and not having in "B" database.
Instance : A
Database : Students
Table : Stud_Info
Instance : B
Database : Stud
Table : Stud_Info.

Hello, Minnu.

First of all,you need a linked server from one instance to the other, so that you are able to read the records from the table.

The second important thing is what kind of differences are you looking for. If you want to find only which keys from one table don't exist in the other, than I would propose to use an FULL OUTER JOIN made on the keys. This way you will be able to see which keys are in the Students table but not in the Stud, which are in the Stud table and not in Students and, of course, which keys are common.

Based on this join you could also check for the common keys if other columns have different values.

The query should be something like :

Select * from Students.dbo.Stud_Info a

full outer join InstanceB.Stud.dbo.Stud_Info b

on a.key = b.key

where (a.key is null) -- records only in the table from the instance B

or (b.key is null) -- records only in the table from the instance A

There is always something new to learn.
My personal SQL Blog[/url]

Minnu SSCertifiable Points: 6451 More actions · Answer 13

Hello Sir,

finally i've configured Linked Server.

but am unable to execute the query.

using below syntax:

SELECT * FROM serverA.database.owner.TableName

Union

SELECT * FROM serverB.database.owner.Tablename

Server name am gettting from "SELECT @@SERVERNAME" is it OK.

David McKinney SSChampion Points: 10472 More actions · Answer 14

only use the server name for the linked server instance - not for the 'home' instance.

also I mentioned EXCEPT not union.