I need to insert multiple records into a table based on multiple values found in a single text field retrieved from a select statement. Currently I am using an INSERT INTO and SELECT statement where by each record in the select statement forces an INSERT INTO. However, I need to be able to insert multiple records, as opposed to 1, based on multiple values I parse out of a string of text. Here's bascially what I need to do:
INSERT INTO tableA
Select personid, name, contacts, status
- Parse contacts
- Determine the number of contacts in the string (see contacts string below)
- Insert 1 record per contact.
The logic should be something like this:
do while
insert
end
From tableB
The contacts field is varchar, and can have 3 different contacts listed in the field as follows:
contacts = 'Jim phone 333-222-1111, John 777-888-9999, Joe 444-555-6666"
I need to insert a record for each contact listed in the string - Jim, John, and Joe.
I'm having trouble clearly explaining this so please let me know if more information is required.
I'm using MS SQL 2012.
