Comparision of tables in two instances

  • 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.

  • 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

  • Hi Team,

    Sample query Please........

  • 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

  • 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....

  • 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

  • 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

  • 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

  • select * from Stud_Info

    left inner join SVSINST\SR2002.students.stud_Info

  • 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

  • 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)

  • ....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.

  • 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]

  • 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.

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

    also I mentioned EXCEPT not union.

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

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