query to populate child table

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • dwightsmail@gmail.com
    #1

    query to populate child table


    hi all, can anyone help me?

    I am a relative newbie to sql server and I am more familiar with
    Enterprise Manager than QA. I have made many many access databases
    though. I am making an asp.net application where by there are a set
    number of users, about 80, each one logs in and manages information
    within their department.

    To get them started a manager has written 10 different hazards that
    will apply to all of the departments, and he has written consequences
    and controls for the hazards. Each department must have this
    information as each will manage and deal with them differently

    The hazard information is stored in a main 'hazards' table, and the
    consequences and controls are stored in related tables linked by the
    'hazardID' from the main table to a foreign key 'hazardID' in the
    related tables

    What i want to know is if there is a relatively simple way of using a
    query to populate the 10 hazards to each department, and to also
    include the related table links, i dont mind renaming the departments
    names to match each hazard, but i do not want to have to relink the
    related tables manually

    If anyone can give me any advice to get me started i will be incredibly
    grateful

    thank you


    Table information is below

    Hazards
    ------------
    HazardID - identity key field
    Hazard - varchar
    Department - varchar

    Consequences
    ----------------------
    ConsequenceID - identity key field
    HazardID - FK
    Consequence - varchar

    Controls
    ------------
    ControlID - identity key field
    HazardID - FK
    Control - varchar



    dwight

    Tags: None
    • Erland Sommarskog
      #2
      Re: query to populate child table

      (dwightsmail@gm ail.com) writes:
      What i want to know is if there is a relatively simple way of using a
      query to populate the 10 hazards to each department, and to also
      include the related table links, i dont mind renaming the departments
      names to match each hazard, but i do not want to have to relink the
      related tables manually
      INSERT Hazards(HazardI D, Hazard, Department)
      VALUES (1, 'Fog', 'This Dept')

      Oh, so this won't work, because you have an IDENITY column, but it
      will be a lot easier if you don't have an IDENTITY.
      Consequences
      ----------------------
      ConsequenceID - identity key field
      HazardID - FK
      Consequence - varchar
      If there can be more than once consequence per hazard, I think the key
      should be (HazardID, ConsequenceNo).


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • dwightsmail@gmail.com
          #3
          Re: query to populate child table


          I am getting close to understanding that, but how do I take the
          consequence and control table information with the hazard insert? Will
          the related consequences be inserted with the 'Fog'?

          Thank you for helping me

          I dont want to have to enter the hazards 80 times if I can learn this
          skill

          :)


          Erland Sommarskog wrote:
          (dwightsmail@gm ail.com) writes:
          What i want to know is if there is a relatively simple way of using a
          query to populate the 10 hazards to each department, and to also
          include the related table links, i dont mind renaming the departments
          names to match each hazard, but i do not want to have to relink the
          related tables manually
          >
          INSERT Hazards(HazardI D, Hazard, Department)
          VALUES (1, 'Fog', 'This Dept')
          >
          Oh, so this won't work, because you have an IDENITY column, but it
          will be a lot easier if you don't have an IDENTITY.
          >
          Consequences
          ----------------------
          ConsequenceID - identity key field
          HazardID - FK
          Consequence - varchar
          >
          If there can be more than once consequence per hazard, I think the key
          should be (HazardID, ConsequenceNo).
          >
          >
          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
          >
          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinf...ons/books.mspx

            Comment

            • Erland Sommarskog
              #4
              Re: query to populate child table

              (dwightsmail@gm ail.com) writes:
              I am getting close to understanding that, but how do I take the
              consequence and control table information with the hazard insert? Will
              the related consequences be inserted with the 'Fog'?
              I'm not sure that I understand the question. But it's not that if you
              enter a row in Hazards, that there automatically will be a row in
              Consequences as consequences. Unless, that is you set up a trigger. But
              since the consequence data need more data, that is not really meaningful.

              Some people prefer to use the Open Table functionality that is in
              Enterprise Manager or SQL Server Management Studio where you can enter
              data in a grid. Personally, I find that about slower than typing up a
              number of INSERT statments with help of some copy-and-paste skills.




              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                Previous template Next
                Working...