Following is my scenario where i am trying to do this the best possible way , i tried in SSIS using lookups but dint work out properly (low perfomance) Table Name: Subtransaction Fields : TransactionNo , old pay , new pay , old customer , new customer based on this data i need to update the transaction table with millions of records Table Name :MainTransaction Fields: TransactionNo,Pay,Customer I need to update the records matching on the transaction There are few conditions though. 1.pick up those records from the subtransaction where old pay and new pay are not the same , then update those records in the main transaction based on the transaction no 2.similarly pick up those records from the subtransaction where old customer and new customer are not same , then update those records in the main transaction based on the transaction no Now i need to insert the transaction nos that were updated in to a log table LogTable TransactionNo,UpdatedTime Can some one help me with this please?
You can simply use update from join syntax though with SQL Server 2008 you could also opt to use MERGE instead. The idea is that you create a table variable which will hold the info about the records affected by the transaction, so you can then insert them into the log table after the update is executed. I assume that the TransactionNo is unique in the SubTransaction, because if not then you might end up with updating the single record more than once, in which case some additional measures would be required for the update script to work properly. Here is the script: begin tran; -- declare table variable to hold the info about -- the records in the MainTransaction table which -- are subjected to the update statement declare @updated table ( TransactionNo varchar(64) not null, UpdatedTime datetime not null ); -- this update is limited to only those records in the -- MainTransaction table which have their SubTransaction -- counterparts with changed Pay or Customer. It is safe -- to just issue a plain set instead of worrying about the -- case clauses in the update statement update mt set Pay = st.NewPay, Customer = st.NewCustomer output inserted.TransactionNo, getdate() into @updated from MainTransaction mt inner join SubTransaction st on mt.TransactionNo = st.TransactionNo where st.OldPay st.NewPay or st.OldCustomer st.NewCustomer; -- insert info about affected records into your log table insert into LogTable (TransactionNo, UpdatedTime) select TransactionNo, UpdatedTime from @updated; commit tran; go Hope this helps, Oleg