Tuesday, March 13, 2012 8:38 PM
I have 2 tables and would like to update the 2nd table based on the first table. Information is added into Table1(TblDetailStoreInformation) daily. The information such as StoremanagerName and or LocNumber or StoreName is likely to change.
Table2(TblStoreSummary) contains some summary data from table 1
Table 1 ---TblDetailStoreInformation -StoreNumber, StoreName, LocNumber, StoreManagerName....... plus other fields
Table2 ---TblStoreSummary -- StoreNumber, StoreName,LocNumber, StoreManagerName,LastDateUpdate
What am trying to do is update and or insert data into table 2 (TblStoreSummary). If the store information exists and nothing has changed since its last insert then do nothing else if we have some information change then update the column and the LastDateUpdate is the getdate(). If there is a new store information in table1(TblDetailStoreInformation) then insert that information in table2.
- Moved by Peja TaoModerator Wednesday, March 14, 2012 8:01 AM (From:SQL Server Setup & Upgrade)
Tuesday, March 13, 2012 11:14 PM
If you use SQL 2008 or above then consider using MERGE
- Proposed As Answer by Mr. WhartyMicrosoft Community Contributor Wednesday, March 14, 2012 2:12 AM
Wednesday, March 14, 2012 8:24 AMModerator
If you are using SQL Server 2005, you can use OUTPUT clause of an UPDATE statement to mimick MERGE Statement.
Example is here.
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Thursday, March 15, 2012 6:44 AMConsider using trigger on Table1's update and insert.
Thursday, March 15, 2012 10:46 AM
What you want to do can easily be done firing Trigger on Table1.
Create an after Insert trigger on Table1 and check data which is inserted in Table1 using which perform your Updation or Insertion on Table2
Thursday, March 15, 2012 11:40 AMModerator
I agree with all posters,
Here is a SQL trigger which works after insert or update on information table.
Within the trigger, summary data is inserted or updated using SQL Merge command . Here is an other tsql Merge example : http://www.kodyaz.com/articles/sql-server-2008-t-sql-merge-statement-example.aspx
Alter Trigger tr_TblDetailStoreInformation on TblDetailStoreInformation After Insert, Update AS begin Merge TblStoreSummary Using ( select i.StoreNumber, i.StoreName, i.LocNumber, i.StoreManagerName from inserted i ) MergeData ON TblStoreSummary.StoreNumber = MergeData.StoreNumber WHEN MATCHED THEN UPDATE SET TblStoreSummary.StoreName = MergeData.StoreName, TblStoreSummary.LocNumber = MergeData.LocNumber, TblStoreSummary.StoreManagerName = MergeData.StoreManagerName, TblStoreSummary.LastDateUpdate = Getdate() WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (StoreNumber, StoreName, LocNumber, StoreManagerName, Getdate()); end goI hope that summarizes all notes of the above posters
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, March 20, 2012 8:31 PM