wk_lp
asked on
How to insert multiple rows to a child table in a parent/child relationship in sql Server 2000?
Hi,
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.
t_product:
id name price
1 tester $2
2 spray $5
3 gel $4
t_details:
id partsName
1 bottle
1 cap
1 content
2 cap
2 content
3 content
The user entry form fields include:
product name:
product part 1:
product part 2:
product part 3:
product price:
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.
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.
t_product:
id name price
1 tester $2
2 spray $5
3 gel $4
t_details:
id partsName
1 bottle
1 cap
1 content
2 cap
2 content
3 content
The user entry form fields include:
product name:
product part 1:
product part 2:
product part 3:
product price:
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.
ASKER
Thanks for your sample. Can you also give me some samples on how to use this temp table and insert each item into final table?
Thanks again,
Thanks again,
Try this:
insert into t_details (id, partsName) (select @ID, TBL_TEMP.Item FROM FN_SPLIT(@CSVList) as TBL_TEMP)
Where @ID is the t_product.id you got from scope_identity and @CSVList is the csv varchar list to insert.
insert into t_details (id, partsName) (select @ID, TBL_TEMP.Item FROM FN_SPLIT(@CSVList) as TBL_TEMP)
Where @ID is the t_product.id you got from scope_identity and @CSVList is the csv varchar list to insert.
ASKER
Hi,
I know the insert statement for a single record. But I suppose you need to loop through the temp table to insert all records one by one and how to do that? Don't you need to use cursors or something inside the stored proc after you split the cvs into a table?
Thanks?
I know the insert statement for a single record. But I suppose you need to loop through the temp table to insert all records one by one and how to do that? Don't you need to use cursors or something inside the stored proc after you split the cvs into a table?
Thanks?
This procedure will insert a record for each values return after the split function. If you pass the string 'Test1,Test2,Test3' then three records will be inserted all using the same id. I've tested this and should work when incorporated with the rest of your procedure. If you need help doing this I'd be happy to help.
ASKER
Hi,
Thanks for your reply. I have a question about your fn_split function. Currently it only creates 1 column named "item". What if I pass 2 csv parameters? eg. call fn_split('cap,content,bott le','group A,groupB,g roupC')
So the final table will have 3 columns:
ProductID partName PartGroup
1 cap groupA
1 content groupB
1 bottle groupC
Sorry I didn't think of this question earlier. Would you please modify the fn_split function for me so I can have partname with related groupNames?
Thanks again for your help.
Thanks for your reply. I have a question about your fn_split function. Currently it only creates 1 column named "item". What if I pass 2 csv parameters? eg. call fn_split('cap,content,bott
So the final table will have 3 columns:
ProductID partName PartGroup
1 cap groupA
1 content groupB
1 bottle groupC
Sorry I didn't think of this question earlier. Would you please modify the fn_split function for me so I can have partname with related groupNames?
Thanks again for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE FUNCTION FN_SPLIT (
@ItemList varchar(5000)
)
RETURNS
@ParsedList table (
Item varchar(50)
)
AS
BEGIN
DECLARE @Item varchar(10), @Pos int
SET @ItemList = LTRIM(RTRIM(@ItemList))+ ','
SET @Pos = CHARINDEX(',', @ItemList, 1)
IF REPLACE(@ItemList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Item = LTRIM(RTRIM(LEFT(@ItemList
IF @Item <> ''
BEGIN
INSERT INTO @ParsedList (Item)
VALUES (@Item)
END
SET @ItemList = RIGHT(@ItemList, LEN(@ItemList) - @Pos)
SET @Pos = CHARINDEX(',', @ItemList, 1)
END
END
RETURN
END