question

Trigger is the best option here. The other methods which you are trying to do may lead to performance issues

Your question might point to a more complex requirement in the background tht I am not allowing for but in my opinion I would consider how you want to have the data in the two tables. (Just as a side not, you start describing that you data is in x and needs to go to y but then the bullet points seem to refer to them in the reverse scenario. maybe I am reading it wrongly?) Why not move the whole table from x to y? How often does the data in y need to be updated, or a better way to ask is How up to date must the data in y be? It may be a lot of data to move but if you can accept a daily refresh then moving the whole table is no big deal. I think there isnt enough information to make a complete recomendation here but having ALL the information might be too much for a forum type of question... Personally I would put quite a bit of effort into avoiding triggers.

Another option would be to consider introducing extended transactions through a program like [BizTalk][1]. That way you can commit changes to the table that you have, and also commit those same changes to another table, all as part of a single transaction that can be rolled forward or back but without blocking. [1]: http://www.microsoft.com/en-us/biztalk/

OK - the script below is what I think will work. The objective is to populate an 'Event Log' table that records when there is a new staff record, or if an existing staff record is changed. Ref_StaffData is todays current staff listing, whilst Ref_StaffData_Backup is yesterday's listing - changes are identified by comparing the two. My original query was based on the idea that the Event Log would be populated by Insert and Update triggers on Ref_StaffData, but that wasn't working because the table emptied and repopulated every day, so all we'd get would be an insert trigger for every record in the table. I was trying to find a way around this. However, the script below avoids the need for triggers, by making the comparison and then updating Event Log directly. Btw - the staff table is only a couple of thousand records, with only a handful of changes on any given day, so efficiency isn't an issue. Thanks again for all your help. :-) -- Set up table variable to be used to populate IDM_event_log declare @changedStaff table (EmpNo int, ChangeType varchar(1)) -- Set up interim temp tables that will be used to populate the table variable select top 0 * into #NewMinusOld from Ref_StaffData select top 0 * into #OldMinusNew from Ref_StaffData -- Populate #NewMinusOld with records in Ref_StaffData that are different -- from those in yesterday's backup -- (Note, this will include new and changed records but won't distinguish -- between them) Insert into #NewMinusOld Select * from Ref_StaffData Except Select * from Ref_StaffData_Backup -- Populate #OldMinusNew with records in yesterday's backup that are different -- from those in today's Ref_StaffData. -- (Note, this will include changed and deleted records but won't distinguish -- between them) Insert into #OldMinusNew Select * from Ref_StaffData_Backup Except Select * from Ref_StaffData -- Populate @changedStaff with EmpNo, and ChangeType 'U' for any staff in -- #NewMinusOld, but not in #OldMinusNew - that is, any New staff record -- added since yesterday's backup. Insert into @changedStaff Select [Employee Number], 'N' from #NewMinusOld where [Employee Number] not in (Select [Employee Number] from #OldMinusNew) -- Populate @changedStaff with EmpNo, and ChangeType = 'U' for any staff -- in both #NewMinusOld and #OldMinusNew - that is, any staff record that -- has changed since yesterday's backup. Insert into @changedStaff Select [Employee Number], 'U' from #NewMinusOld where [Employee Number] in (Select [Employee Number] from #OldMinusNew) -- Now drop the interim temp tables, leaving the table variable to be used -- in the remaining stages of this procedure. drop table #NewMinusOld drop table #OldMinusNew -- Record the changes in the event log - this is the ultimate objective of the -- exercise Insert IDM_event_log (table_key, event_type, table_name) Select table_key = 'Emp_No = ' + cast(c.EmpNo as varchar(6)), CASE When c.ChangeType = 'U' then 6 When c.ChangeType = 'N' then 5 END as event_type, table_name = 'Ref_StaffData' From @changedStaff c Select * from dbo.IDM_event_log