none
If exists update else insert

    Question

  • 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. 


    OJMP

    • Moved by Peja Tao Wednesday, March 14, 2012 8:01 AM (From:SQL Server Setup & Upgrade)
    Tuesday, March 13, 2012 8:38 PM

Answers

  • Hello,

    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
    go
    I hope that summarizes all notes of the above posters

    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Thursday, March 15, 2012 11:40 AM

All replies

  • Hi

    If you use SQL 2008 or above then consider using MERGE

    http://technet.microsoft.com/en-us/library/bb510625.aspx


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Tuesday, March 13, 2012 11:14 PM
  • @ojmp2001,

    If you are using SQL Server 2005, you can  use OUTPUT clause of an UPDATE statement to mimick MERGE Statement.

    Example is here.


    Best Regards,
    Peja

    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.

    Wednesday, March 14, 2012 8:24 AM
  • Consider using trigger on Table1's update and insert.
    Thursday, March 15, 2012 6:44 AM
  • Hello ojmp2001

    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 10:46 AM
  • Hello,

    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
    go
    I hope that summarizes all notes of the above posters

    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Thursday, March 15, 2012 11:40 AM