Retrieving records from main database and logs database

Tags:
SQL Server
Our system has one main database (jadeappdbase) but the system logs (logs per transaction/process) are stored on a separate database on a monthly basis (a specific database is created every start of the month to hold all the logs for the said month).

How will I get certain records from the databases, such that the records that I get from the logs database exist in the main database? (i.e., the cell number that transacted in January is still in the main database 'jadeappdbase').

Conversely, how will I get all the records in the main database that are not existing in the logs database (i.e., the cell number that exists in the main database did not make a transaction for a certain period of time, considering that every month is a different database)?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You have a multi-part question. I can only answer one part.

The part I cannot answer is how to connect your two databases – each RDBMS vendor has his own “communication component”

What I can address is how to get the data.

You are looking for an JOIN, an INNER JOIN gets matches tows and an OUTER JOIN where you get data from one table that does not match data from another. So scope out OUTER JOIN in your manual (I don’t know SQL Server syntax). Here is a sample for DB2:
<pre>
— OUTER JOIN EXAMPLE
— OUTER JOIN IS AN INNER JOIN WITH THE “MISSING ROWS”
— USES (MAY CREATE) TABLES TSLP101 & TSLP102
— TSLP101 CONTAINS 3 ROWS, WHERE IDNUM (PK) = 1 , 2 , 3
— TSLP102 CONTAINS 3 ROWS, WHERE NUMID (FK) = 2 , 3 , 3

—** LETS DO A REGULAR JOIN (NOW CALLED AN INNER JOIN)
—** WHERE WE FIND THE ROWS IN TWO TABLES THAT MATCH.
—**
SELECT T1.NAME “T1_NAME” ,
T2.CODE “T2.CODE”
FROM TSLP101 T1 ,
TSLP102 T2
WHERE T1.IDNUM = T2.NUMID
ORDER BY T1.IDNUM ;

—** NOW LETS TRY A SIMILAR QUERY, BUT USE AN OUTER JOIN. THIS
—** ALLOWS US TO NOT ONLY SHOW THE MATCHING ROWS, BUT THE NON-
—** MATCHING ROWS ALSO. (THE NON-MATCHING ROWS CAN ONLY COME
—** FROM THE LEFT TABLE.)
—**
SELECT T1.NAME “T1_NAME” ,
T2.CODE “T2.CODE”
FROM TSLP101 T1
LEFT OUTER JOIN
TSLP102 T2
ON T1.IDNUM = T2.NUMID
ORDER BY T1.IDNUM ;

—** NOW, A RIGHT OUTER JOIN
—**
SELECT T1.NAME “T1_NAME” ,
T2.CODE “T2.CODE”
FROM TSLP101 T1
RIGHT OUTER JOIN
TSLP102 T2
ON T1.IDNUM = T2.NUMID
ORDER BY T1.IDNUM ;

—** NOW, A FULL OUTER JOIN
—**
SELECT T1.NAME “T1_NAME” ,
T2.CODE “T2.CODE”
FROM TSLP101 T1
FULL OUTER JOIN
TSLP102 T2
ON T1.IDNUM = T2.NUMID
ORDER BY T1.IDNUM ;
—**
</pre>

and Here is an ORACLE example:
<pre>
CREATE TABLE TJOIN1
(FNM VARCHAR2(7)
,LNM VARCHAR2(8)
,IDNUM DEC(3,0) );
CREATE TABLE TJOIN2
(IDNUM DEC (3,0), OTHER_DATA VARCHAR2(5) );

—** SPECIFY “INNER JOIN” TO GET MATCHING ROWS
SELECT T1.FNM, T1.LNM, T1.IDNUM, T2.OTHER_DATA
FROM TJOIN1 T1 INNER JOIN TJOIN2 T2
ON T1.IDNUM = T2.IDNUM;

—** SPECIFY “LEFT OUTER JOIN” TO GET MATCHING ROWS AND
—** NON-MATCHING FROM THE TABLE ON THE LEFT
SELECT T1.FNM, T1.LNM, T1.IDNUM, T2.OTHER_DATA
FROM TJOIN1 T1 LEFT OUTER JOIN TJOIN2 T2
ON T1.IDNUM = T2.IDNUM;

—** SPECIFY “RIGHT OUTER JOIN” TO GET MATCHING ROWS AND
—** NON-MATCHING FROM THE TABLE ON THE RIGHT
SELECT T1.FNM, T1.LNM, T1.IDNUM, T2.OTHER_DATA, T2.IDNUM
FROM TJOIN1 T1 RIGHT OUTER JOIN TJOIN2 T2
ON T1.IDNUM = T2.IDNUM;

—** SPECIFY “FULL OUTER JOIN” TO GET MATCHING ROWS AND
—** NON-MATCHING FROM BOTH TABLES
SELECT T1.FNM, T1.LNM, T1.IDNUM, T2.OTHER_DATA, T2.IDNUM
FROM TJOIN1 T1 FULL OUTER JOIN TJOIN2 T2
ON T1.IDNUM = T2.IDNUM;

—** TO ADD MORE CONDITIONS TO THE SEARCH CRITERIA ADD THEM TO
—** THE “ON” CLAUSE.
—** THE NEXT SQL STMNT IS THE “LEFT OUTER JOIN” FROM ABOVE
—** WITH ONE ADDITIONAL CONDITION.
SELECT T1.FNM, T1.LNM, T1.IDNUM, T2.OTHER_DATA
FROM TJOIN1 T1 LEFT OUTER JOIN TJOIN2 T2
ON T1.IDNUM = T2.IDNUM
AND T2.OTHER_DATA = ‘AAAA’ ;

</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following