How to insert multiple rows to a child table in a parent/child relationship in sql Server 2000?
Posted on 2007-03-21
This is related to my first open question.
I have 2 tables: One is called t_product and the other is called t_details. t_product has id (identity column), name, price etc. and t_details has productID(it comes from id of t_product), partsName.
id name price
1 tester $2
2 spray $5
3 gel $4
The user entry form fields include:
product part 1:
product part 2:
product part 3:
To add a record, I have a stored procedure to insert the product name and price into t_product since it's 1-1 relationship. Then I can get the scope_identity for inserting different parts into t_details. But I have trouble adding multiple rows in t_details. My plan is to concatenate product parts into a comma separated string and pass it to a stored procedure. Inside the stored procedure, I need a way to separate the string and then insert all items into t_details. It's one-to-many relationship. I haven't figured out how to create that kind of stored procedure. Do I need to separate the string and put them into a temp table then use cursor to loop the table and finally insert item to the final table one by one? Can anybody tell me in detail how to do that?
Thanks a lot.