Solved

SP to insert a list

Posted on 1998-12-07
2
190 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:amnh
2 Comments
 
LVL 3

Accepted Solution

by:
hakyemez earned 200 total points
ID: 1092120
/*@yourstring = '1,2,4,6'*/
declare @id int
declare @value int
declare @pos int,@newstr varchar(255),@bExit int
select @pos=CharIndex(",",@yourstring)
select @id=Convert(int,SubString(@yourstring,1,@pos-1))
select @newstr=SubString(@yourstring,@pos+1,datalength(@yourstring)-@pos)
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 table (Id,Value) values (@id,@value)
        select @newstr = SubString(@newstr,@pos+1,datalength(@newstr)-@pos)
    end
    else select bExit=0
end



0
 

Author Comment

by:amnh
ID: 1092121
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 52
Help with Merge Join and Conditional Split in SSIS 6 76
Download ms sql express. 2 23
Query for timesheet application 3 15
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

790 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