asked on

SP to insert a list

I am attempting to write a stored procedure to insert multiple records based on a list stored in a string (varchar). The first parameter is the an ID number and the second parameter is the list (ie "1,2,4,6"). I would like suggestion on either parsing the list then running many insert of the type INSERT table (ID,Value) VALUES (@Id,@Value) or can I do a batch insert with nested selects.

ASKER CERTIFIED SOLUTION

hakyemez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amnh

ASKER

Good Answer.
I changed slightly by the passing the @id as seperate parameter.
@ID integer,
@yourstring varchar(255)
as
declare @value int
declare @pos int,@newstr varchar(255),@bExit int
select @pos=CharIndex(',',@yourstring)
select @newstr=@yourstring
select @bExit=-1
while @bExit=-1
begin
      select @pos=Charindex(',',@newstr)
      if @pos<>0
      begin
            select @value = Convert(int,SubString(@newstr,1,@pos-1))
            insert into tblUserListMem (User_ID,UserList_ID) values (@id,@value)
            select @newstr = SubString(@newstr,@pos+1,datalength(@newstr)-@pos)
      end
      else
      begin
            select @bExit=0
            select @value = Convert(int,@newstr)
            insert into table (User_ID,UserList_ID) values (@id,@value)
      end
end