Solved

SP to insert a list

Posted on 1998-12-07
2
187 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Microsoft SQL query 7 38
Choosing SSD drives for SQL Server 32 77
MS SQL Backup 24 70
Update foreign key reference after insert 9 30
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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
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.

708 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

11 Experts available now in Live!

Get 1:1 Help Now