Solved

How to insert multiple rows to a child table in a parent/child relationship in sql Server 2000?

Posted on 2007-03-21
7
345 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:wk_lp
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:flipz
Comment Utility
This sql function will return a temp table from a csv list - this will get you started:

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, @Pos - 1)))
                  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
0
 

Author Comment

by:wk_lp
Comment Utility
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,
0
 
LVL 11

Expert Comment

by:flipz
Comment Utility
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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:wk_lp
Comment Utility
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?
0
 
LVL 11

Expert Comment

by:flipz
Comment Utility
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.
0
 

Author Comment

by:wk_lp
Comment Utility
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,bottle','groupA,groupB,groupC')
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.
0
 
LVL 11

Accepted Solution

by:
flipz earned 125 total points
Comment Utility
This function will only work if both lists have the same number of values. If the first list has more than the second it will throw an error. If the second list is longer than the first then it will only return the number of records based on the length of the first list. All in all is should meet your requirements:

CREATE FUNCTION FN_SPLIT_DUAL (
      @ItemList1 varchar(5000),
      @ItemList2 varchar(5000)
)
RETURNS
@ParsedList table (
      Item1 varchar(100),
      Item2 varchar(100)
)
AS
BEGIN
      DECLARE @Item1 varchar(100), @Item2 varchar(100), @Pos1 int, @Pos2 int

      SET @ItemList1 = LTRIM(RTRIM(@ItemList1))+ ','
      SET @Pos1 = CHARINDEX(',', @ItemList1, 1)

      SET @ItemList2 = LTRIM(RTRIM(@ItemList2))+ ','
      SET @Pos2 = CHARINDEX(',', @ItemList2, 1)

      IF REPLACE(@ItemList1, ',', '') <> ''
      BEGIN
            WHILE @Pos1 > 0
            BEGIN
                  SET @Item1 = LTRIM(RTRIM(LEFT(@ItemList1, @Pos1 - 1)))
                  SET @Item2 = LTRIM(RTRIM(LEFT(@ItemList2, @Pos2 - 1)))
                  IF @Item1 <> ''
                  BEGIN
                        INSERT INTO @ParsedList (Item1, Item2)
                        VALUES (@Item1, @Item2)
                  END
                  SET @ItemList1 = RIGHT(@ItemList1, LEN(@ItemList1) - @Pos1)
                  SET @ItemList2 = RIGHT(@ItemList2, LEN(@ItemList2) - @Pos2)
                  SET @Pos1 = CHARINDEX(',', @ItemList1, 1)
                  SET @Pos2 = CHARINDEX(',', @ItemList2, 1)
            END
      END      
      RETURN
END
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now