March 20, 2013 at 6:20 am
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?
March 20, 2013 at 6:26 am
... 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...
March 20, 2013 at 6:32 am
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?
March 20, 2013 at 6:42 am
SQL_NuB (3/20/2013)
the values that are different so for exampleif 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 ...
March 20, 2013 at 6:45 am
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 don't match
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply