Insert into several tables

[Sheila Ratnam]

Hi all,

I am creating a Stored Procedure (in SQL Server 6.5) to add new information to several related tables at one go. Should I write a separate Insert statement for each table?
What happens if the insert in one of the tables fails?

If you could direct me to an example, I'll be obliged.

Thanks in advance,
Sheila

[Chris]

For optimization, write separate INSERT Statements for each table. Start your inserts from the parent, then work your way down to it's children/ancestors. Essentially walk the referential integrity down the data model.
Validate the insert with (@@ERROR = 0 AND @@ROWCOUNT >= 1). This will ensure that there were no errors and the rowcount ensures that at least 1 row was inserted.

If the validation fails, then raise an error, because if something fails, alls subsequent inserts to child tables will also fail.

Hope this helps.

------------
Sheila Ratnam at 8/21/00 1:38:08 PM


Hi all,

I am creating a Stored Procedure (in SQL Server 6.5) to add new information to several related tables at one go. Should I write a separate Insert statement for each table?
What happens if the insert in one of the tables fails?

If you could direct me to an example, I'll be obliged.

Thanks in advance,
Sheila