February 20, 2010 at 8:13 am
Greetings,
I'm currently feeling pretty frustrated with SQL Server 2005 at the moment. After failing miserably to get SSIS to do Type-3 updates, ie record which columns have been updated after an UPDATE statement has been executed, I have had to resort to writing T-SQL statements to try and cater for Type-3 updates, but despite being reasonably good at T-SQL coding, I'm struggling getting this to work.
Basically what I want is something like this ...
.....
UPDATE tbl_EMP_DELTAS
SET ColsChanged = 'Firstname' and a.EmpID = b.EMPID
WHERE EXISTS
(select b.EmpID from EMPLOYEE b where b.EmpStatus = 'Active')
...
But, for the life of me I can not seem to get the 'a.EmpID = b.EMPID' (in line 2 above) to match up the records (join).
What am I doing wrong here? Alas, it's late at night here!
Most appreciated.
February 20, 2010 at 8:39 am
ben i hope this helps;
this is the syntax i would use to update from a different table; it would be different if this is inside a trigger;
i *think* your current sql is simply trying to update one column based on the active flag in another table, is that right? or are you trying to say the firstname changed as some kind of audit? how do you know the first name changed?
i don't think it's just because of the "Active" flag, right? that sort of stuff is usually done in a trigger, where you compare INSERTED.FirstName <> DELETED.FirstName; let me know if that is what you are trying to do instead.
UPDATE a --we just alias the table to be updated
SET a.ColsChanged = 'Firstname'
FROM tbl_EMP_DELTAS a --just an alias
LEFT OUTER JOIN EMPLOYEE b --join this tab le for the update criteria
ON a.EmpID = b.EMPID --the join for a to b, based on empid
WHERE b.EmpStatus = 'Active' --only specific records that are "Active"
Lowell
February 20, 2010 at 4:41 pm
Thanks for your reply to my post.
Yes, I have tried using a SQL Trigger and if I remember it worked when an UPDATE statement was run, but there is interest in being able to compare one data load .vs. another data load, so logging historical data this time around is important, so really trying doing multiple INSERT statements rather than an UPDATE. But having said that I want to UPDATE the colFieldsModified field with the data differences, ie which columns are different between the current record for e.g. ID = 1 and the previous record for ID = 1.
BTW, you're partly right if you are eluding to the fact that the SQL I provided is not all the SQL that I am working with. I have done this because there is quite a bit of SQL in my current script and so I am only providing that part that doesn't appear to be working. Basically what I am trying to achieve is something like the following ...
UPDATE tbl1 a
SET colFieldsModified = 'x'
WHERE a.IDNum in (select b.IDNum from tbl2 where b.empstatus = 'Active')
Should be simple enough - alas it hasn't proved to be so far!
The very important part is the matching on IDNum (from tbl1 and tbl2 as shown above) and the ability to UPDATE tbl1 based on the existence of there being a match in values between a.IDNum = b.IDNum. Obviously if the SQL above worked, I would be here logging this POST, so any suggestions on how to re-work this to get it to work would be fantastic. Unfortunately, I've been a bit spoilt in the last 18 months having had SSIS handle a lot of the complexities (even if minor) via the Slowly Changing Dimension control. As mentioned though, Type 1 and Type 2 updates are supported, but Type 3 - forget about it!
Thanks in advance.
Ben
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply