Sql Server: Insert comma separated values in different rows


This is a code snippet for a Stored Procedure which accepts a comma separated values in parameter @Retailers which needs to be stored in a table with different rows. One fine day i had such type of requirement and i fount this post in a forum. I felt like it to share with you.

Declare @MakerBlockID int, @Pos int, @phrase Varchar(4000)
— Insert the maker details
Insert into MakerBlock Values (@Routingnum, @Accountnum, @MinDecAmt, @MaxDecAmt)
— Get the Id from MakerBlock
Select @MakerBlockID = SCOPE_IDENTITY( )

Set @phrase = Replace(@Retailers,’ ‘ ,”)
While Len(@phrase) >0
Begin
Set @pos = CHARINDEX (‘,’,@Phrase)
if @pos > 0
Begin
Insert into MakerBlockRetailers Select @MakerBlockID, Left(@Phrase,@pos-1)
Set @Phrase = Right(@Phrase, Len(@phrase)-@pos)
end
else
Begin
Insert into MakerBlockRetailers Select @MakerBlockID, @Phrase
Set @phrase=”
end
End

Thanx to Say Hey Kid for providing this code.

Original reference: http://www.sqlservercentral.com/Forums/Topic282332-8-1.aspx

Advertisements

2 thoughts on “Sql Server: Insert comma separated values in different rows

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s