Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help with query - am I going down the right path on this Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 6:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:21 AM
Points: 176, Visits: 346
I'm working on a query that will be comparing columns in tables in 2 seperate databases. If the where clause is met, I need to create a log file and then email it at the end of the procedure.
here is my query:

SELECT db1.*, db2.*
FROM db1..ORGANIZATION db1
inner join
db2..ORGANIZATION db2
ON db1.ORG_ID = db2.ORG_ID
where db1.TITLE <> db2.TITLE
or
db1.CODE <> db2.CODE
or
db1.ACTIVE <> db2.ACTIVE


if any of the criteria in the where clause is met, I need to create a log file and email it. I'd like to this is using SSIS, however, how can I have a return value of [true] returned in the where statement if something meant, plus the values?

so for example
if db1.organization <> db2.orgranization, I want the values from both databases, return true so I can create a log file, like I mentioned I'd like to do this using SSIS, but if I can't I'll be doing this in a stored procedure that will be kicked off nightly

Am I even going down the right direction with comparing values in the tables?
Post #1433164
Posted Wednesday, March 20, 2013 6:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
... I want the values from both databases, return true


What do you mean by this? Which values?
Also, when you compare your data in the WHERE clause the way you do, be aware if one of the value is NULL it will find no difference...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433169
Posted Wednesday, March 20, 2013 6:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:21 AM
Points: 176, Visits: 346
the values that are different so for example
if Active is true in DB2 and now coming in as FALSE in DB1, I need that.

as for NULL values, I never thought, how can I handle those?
Post #1433175
Posted Wednesday, March 20, 2013 6:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
SQL_NuB (3/20/2013)
the values that are different so for example
if Active is true in DB2 and now coming in as FALSE in DB1, I need that.

as for NULL values, I never thought, how can I handle those?


Single fixed query can only select the same fixed number of columns.
As you select all columns from both ends, so you will get all values.

Nulls can be values by different ways. Most robust and neat one would be:

WHERE   NULLIF(t1.Col1,t2.Col1) IS NOT NULL  OR NULLIF(t2.Col1,t1.Col1) IS NOT NULL  
OR NULLIF(t1.Col2,t2.Col2) IS NOT NULL OR NULLIF(t2.Col2,t1.Col2) IS NOT NULL
OR ...



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433179
Posted Wednesday, March 20, 2013 6:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:21 AM
Points: 176, Visits: 346
Single fixed query can only select the same fixed number of columns.
As you select all columns from both ends, so you will get all values.

Nulls can be values by different ways. Most robust and neat one would be:



I'm only compare 3 -4 values per table, which is now part of my query and I'll only get those values back now and now I'll be writing those to a file,
I'll look at what you provided for NULL values and test it. how can i create my log file though from SQL or even get an code of [true] so i can create the log file? I only want the log file created if the column[s] don't match
Post #1433180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse