Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
inserting to A(different context) and B (different context) tables should reflect in C table should i create C table or i can view A&B data using joins ??

Answered inserting to A(different context) and B (different context) tables should reflect in C table should i create C table or i can view A&B data using joins ??

  • Wednesday, February 27, 2013 2:22 PM
     
     
    Sign In to Vote
    0

    hi all , 

     i will make it very easy to understand my question  visually : 

    i have table SubGoalTask_tb and table EventActivity_tb table by inserting into these two tables i  should see the result in ToDo_tb table 

    i have two option to do that i dont know whether  they  are right or wrong but i will share them with you : please see the below image i mentioned  there things very clearly

    (use ctrl and '+' keys  to zoom in  )  : 

     this question is tricky i m not an expert in sql server just intermediate i can ignore join option because each table has it is own information structure but with that in mind using option two i will have many null or i can later reply null with any thing but still it does not look natural to me but there is no third option to go with  ..  

    your quick help  will be greatly appreciated .Thank you 


    Ali Zaidan





    • Edited by Ali zidan Wednesday, February 27, 2013 2:26 PM change
    •  
     

All Replies

  • Wednesday, February 27, 2013 5:31 PM
     
     
    Sign In to Vote
    0

    Hi Ali -

    Can you explain a bit more?  I am not clear on what you're asking.  Perhaps you can also post some DDL.

    Are you looking for help on how to write the query to generate the final results...or asking for help on how to insert data into multiple tables....??

     - will

    - will

     
  • Wednesday, February 27, 2013 5:31 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Ali,

    Just do an insert using "UNION ALL" to combine the two select statements.

    As the given IDs don't lead to the sub task, I am not sure why you need it. If you need it to "find back" to the sub task then the information is not sufficient.

    INSERT INTO ToDo_tb
	(ToDoType, GoalID, EventID, ToDoTaskName, ToDoDate)
SELECT 'goal' as ToDoType
	  ,g.GoalID
	  ,null as EventID
	  ,ta.SubGoalTaskName
	  ,ta.SubGoalTaskDate
FROM Goal_tb g
INNER JOIN SubGoal_tb sg on sg.GolaID = g.GoalID
INNER JOIN SubGoalTask_tb ta on ta.SubGoalID = sg.SubGoalID
UNION ALL
SELECT 'event' as ToDoType
	  ,null as GoalID
	  ,e.EventID
	  ,ea.EventActivityName
	  ,ea.EventActivityDate
FROM Event_tb e
INNER JOIN EventActivity_tb ea on ea.EventID = e.EventID

    Alex
     
  • Wednesday, February 27, 2013 7:23 PM
     
     
    Sign In to Vote
    0

    yes Alex i did  by mistake  put GoalID and EVentID in TodoTask_tb i was in hurry ....here this is what i meant :


    • Edited by Ali zidan Wednesday, February 27, 2013 7:24 PM change
    •  
     
  • Thursday, February 28, 2013 7:29 AM
     
     
    Sign In to Vote
    0

    well then just exchange the IDs in the statement.

    Use the insert if you want to fill a physical table or just use the select and put it into a view.

    Alex

    • Marked As Answer by Ali zidan Friday, March 01, 2013 12:37 PM
    • Unmarked As Answer by Ali zidan Friday, March 01, 2013 12:37 PM
    •  
     
  • Friday, March 01, 2013 1:02 PM
     
     Answered
    Sign In to Vote
    0

    i found the solution :


    i figuer out how to solve the problem posted  on design level but the  thing making my mind  bobbling is when  :

    i have store procedure created  for insertion , updation  and deletion when user update or insert data to the datagridview these  data should be passed to store procedure  then from there the procedure will continues doing its job i just want to know is it possible to do that  through binding or something  as we do with textbox and combox i can easy passed the values kept in those objects except datagridview ? if so how  ?

    if you just direct me to any recourse i can follow along  

    Thank you for in advance for  your time reading  this post ..

    Regards

    Ali Zidan




    • Edited by Ali zidan Friday, March 01, 2013 1:06 PM change
    • Marked As Answer by Iric WenModerator Thursday, March 07, 2013 8:57 AM
    •  
     
  • Friday, March 01, 2013 1:39 PM
     
      Has Code
    Sign In to Vote
    0

    Why make a copy of the data, you'll just have to keep it in sync.  Create a view instead.

    create view ToDo
as
SELECT 'goal' as ToDoType
	  ,g.GoalID
	  ,cast(null as int) as EventID
	  ,ta.SubGoalTaskName
	  ,ta.SubGoalTaskDate
FROM Goal_tb g
INNER JOIN SubGoal_tb sg on sg.GolaID = g.GoalID
INNER JOIN SubGoalTask_tb ta on ta.SubGoalID = sg.SubGoalID
UNION ALL
SELECT 'event' as ToDoType
	  ,null as GoalID
	  ,e.EventID
	  ,ea.EventActivityName
	  ,ea.EventActivityDate
FROM Event_tb e
INNER JOIN EventActivity_tb ea on ea.EventID = e.EventID
    David

    David http://blogs.msdn.com/b/dbrowne/

     
  • Friday, March 01, 2013 1:57 PM
     
     
    Sign In to Vote
    0

    Thank you for you quick reply ..i will try you code ( view ) option it seems reasonable .. 

    so you mean when user want to insert while his in todo ( view ) i will make an insert to  ( subgoalTask_tb and EventActivyty_tb) behind the scene  and after the insert i will call the view to show the data  ..

    Fine i will try it .. but meanwhile could you please reply to my last  part of the question that i posted last time it is something related to ADO.net  and sqlserver of course  here :

    i figuer out how to solve the problem posted  on design level but the  thing making my mind  bobbling is when  :

    i have store procedure created  for insertion , updation  and deletion when user update or insert data to the datagridview these  data should be passed to store procedure  then from there the procedure will continues doing its job i just want to know is it possible to do that  through binding or something  as we do with textbox and combox i can easy passed the values kept in those objects except datagridview ? if so how  ?

    if you just direct me to any recourse i can follow along  


    Regards

    Ali Zidan

     
  • Friday, March 01, 2013 2:24 PM
     
     
    Sign In to Vote
    0

    Hi Ali,

    not really a t-sql question any more...

    Usually you'll set the datasource property of the datagridview. When then underlying data changes you'll have to reload the data into the datasource, that should refill your datagridview.

    Alex


    • Edited by Alex Vary Friday, March 01, 2013 2:25 PM
    •