Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
If exists update else insert

คำตอบ If exists update else insert

  • Tuesday, March 13, 2012 8:38 PM
     
     
    Sign In to Vote
    0

    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 TaoModerator Wednesday, March 14, 2012 8:01 AM (From:SQL Server Setup & Upgrade)
    •  
     

All Replies

  • Tuesday, March 13, 2012 11:14 PM
     
     Proposed
    Sign In to Vote
    1

    Hi

    If you use SQL 2008 or above then consider using MERGE

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

    Javier Villegas | @javier_vill | http://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

     
  • Wednesday, March 14, 2012 8:24 AM
    Moderator
     
     
    Sign In to Vote
    0

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

     
  • Thursday, March 15, 2012 6:44 AM
     
     
    Sign In to Vote
    0
    Consider using trigger on Table1's update and insert.
     
  • Thursday, March 15, 2012 10:46 AM
     
     
    Sign In to Vote
    0

    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 11:40 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    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