locked
updating multiples tables RRS feed

  • Question

  • Hi ,

    i want to update two tables

    ALTER PROCEDURE [dbo].[SP_updateDetails]
    	-- Add the parameters for the stored procedure here
    @ServiceID int,
    @DayoftheWeek varchar(50),
    @Date datetime,
    @Services varchar(50),
    @Venue varchar(50),
    @ProjectName varchar(50),
    @StartTime varchar(50),
    @EndTime varchar(50),
    @Breakfast varchar(50),
    @Lunch varchar(50),
    @Dinner varchar(50),
    @Total varchar(50),
    @HoursTotal varchar(50)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	
    update DetailsTable set Services=@Services,Venue=@Venue,ProjectName=@ProjectName,StartTime=@StartTime,
    EndTime=@EndTime,Breakfast=@Breakfast,Lunch=@Lunch,Dinner=@Dinner,Total=@Total,HoursTotal=@HoursTotal where ServiceID=@ServiceID and DayoftheWeek=@DayoftheWeek
    END
    ALTER PROCEDURE [dbo].[SP_TimeSheetMain_Insert]
    	
    @EmpID int,
    @EmpName varchar(50),
    @StartDate datetime,
    @EndDate datetime,
    @date datetime,
    @day varchar(50),
    @Service nvarchar(max),
    @Venue varchar(50),
    @StartTime varchar(50),
    @EndTime varchar(50),
    @Breakfast varchar(50),
    @Lunch varchar(50),
    @Dinner varchar(50),
    @DailyTotal varchar(50),
    @Total varchar(50)
    AS
    BEGIN
    	insert into EmpTimesheet(EmpID,EmpName,StartDate,EndDate,date,day,Service,Venue,StartTime,EndTime,Breakfast,Lunch,Dinner,DailyTotal,Total
    ) values (@EmpID,@EmpName,@StartDate,@EndDate,@date,@day,@Service,@Venue,@StartTime,@EndTime,@Breakfast,@Lunch,@Dinner,@DailyTotal,@Total
    )
    
    END
    If i update anything in details table i want to update it into emptimesheet table .Do i need to write another update statement in updatedetails storedprocedure or any other ways that i can simply update emptimesheet.

    Thursday, January 23, 2014 5:57 PM

Answers

  • Are you looking for a permanent way to update table B each time you are updating table A? In other words, any update of table A will lead to an update of Table B? Or sometimes you need to update the tables without dependency?

    For clarification, A trigger is a database element, which execute on a specific event (therefor the name trigger, as the event trigger the action). Once you created a trigger (and it is enable) it will execute on that even every time! In this case Visakh16 suggested the use of trigger which will execute on any update event. I just want to clarify if that is what you want, then this is the answer, but if you need some times to use update without dependency and sometimes with dependency, then you can use a simple query without creating new element on the database, with using an 'OUTPUT INTO'. It will not fit any DDL, but this is very fast and easy way to insert data into table B using an update statement on table A.

    Unfortunately you did not post a DDL+DML, and i dont want to do it for all the columns in your query, therefor i will demonstrate using two simple tables. I hope you will be able to implement this on your table. If you dont, then please post DDL+DML.

    Check is this sample code help you:

    use QQ
    go
    
    CREATE TABLE TableA (
    	TableAID INT PRIMARY KEY,
    	TableAName NVARCHAR(10)
    )
    CREATE TABLE TableB (
    	TableBID int,
    	TableBName NVARCHAR(10)
    )
    GO
    
    select * from TableA
    select * from TableB
    GO
    
    insert TableA values (1, 'a')
    GO
    
    update TableA
    set TableAID = 2, TableAName = 'b'
    OUTPUT INSERTED.TableAID,inserted.TableAName INTO TableB
    
    select * from TableA
    select * from TableB
    GO
    
    DROP table TableA
    DROP TABLE TableB
    GO

    this is the main Idea:

    update TableA
    set TableAID = 2, TableAName = 'b'
    OUTPUT INSERTED.TableAID,inserted.TableAName INTO TableB

    I hope this is helpful :-)


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Thursday, January 23, 2014 9:25 PM
    • Marked as answer by emaak Thursday, January 23, 2014 10:06 PM
    Thursday, January 23, 2014 9:20 PM

All replies

  • One method is to do it via trigger

    something like below

    CREATE TRIGGER Trg_UpdateDetails
    ON DetailsTable
    FOR UPDATE 
    AS
    BEGIN
    UPDATE e
    SET Venue = i.Venue,
    StartTime = i.StartTime,
    EndTime = i.EndTime,
    Breakfast = i.Breakfast,
    Lunch = i.Lunch,
    Dinner = i.Dinner,
    DailyTotal = i.DailyTotal,
    Total = i.Total 
    FROM EmpTimesheet e
    INNER JOIN INSERTED i
    ON i.Service = e.Service
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Jayakumaur (JK) Thursday, January 23, 2014 6:59 PM
    • Marked as answer by emaak Thursday, January 23, 2014 10:06 PM
    • Unmarked as answer by emaak Friday, January 24, 2014 2:52 PM
    Thursday, January 23, 2014 6:54 PM
    Answerer
  • Are you looking for a permanent way to update table B each time you are updating table A? In other words, any update of table A will lead to an update of Table B? Or sometimes you need to update the tables without dependency?

    For clarification, A trigger is a database element, which execute on a specific event (therefor the name trigger, as the event trigger the action). Once you created a trigger (and it is enable) it will execute on that even every time! In this case Visakh16 suggested the use of trigger which will execute on any update event. I just want to clarify if that is what you want, then this is the answer, but if you need some times to use update without dependency and sometimes with dependency, then you can use a simple query without creating new element on the database, with using an 'OUTPUT INTO'. It will not fit any DDL, but this is very fast and easy way to insert data into table B using an update statement on table A.

    Unfortunately you did not post a DDL+DML, and i dont want to do it for all the columns in your query, therefor i will demonstrate using two simple tables. I hope you will be able to implement this on your table. If you dont, then please post DDL+DML.

    Check is this sample code help you:

    use QQ
    go
    
    CREATE TABLE TableA (
    	TableAID INT PRIMARY KEY,
    	TableAName NVARCHAR(10)
    )
    CREATE TABLE TableB (
    	TableBID int,
    	TableBName NVARCHAR(10)
    )
    GO
    
    select * from TableA
    select * from TableB
    GO
    
    insert TableA values (1, 'a')
    GO
    
    update TableA
    set TableAID = 2, TableAName = 'b'
    OUTPUT INSERTED.TableAID,inserted.TableAName INTO TableB
    
    select * from TableA
    select * from TableB
    GO
    
    DROP table TableA
    DROP TABLE TableB
    GO

    this is the main Idea:

    update TableA
    set TableAID = 2, TableAName = 'b'
    OUTPUT INSERTED.TableAID,inserted.TableAName INTO TableB

    I hope this is helpful :-)


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Thursday, January 23, 2014 9:25 PM
    • Marked as answer by emaak Thursday, January 23, 2014 10:06 PM
    Thursday, January 23, 2014 9:20 PM