query to populate child table

[dwightsmail]

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

[Erland Sommarskog]

(dw*********@gmail.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(HazardID, 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, es****@sommarskog.se

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

[dwightsmail]

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:

(dw*********@gmail.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(HazardID, 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, es****@sommarskog.se

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

[Erland Sommarskog]

(dw*********@gmail.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, es****@sommarskog.se

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