How do i insert Comma separated values in different rows.
-
Hi Guys,
I have a stored proc which accepts a comma separted values in parameter @Retailers which needs to be stored in a table with different rows.
Following is the code:::
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
I was wondering is there any better way to do this.
Thanks
Brij
-
That's effectively how it would work best. What I'd do different is encapsulate the string parsing in a reusable function though-- e.g. StringField( string, delimiter, positionnumber) to extract the substrings.
(There should be a few of these utility functions here on the site).
(The only faster implementation would be to dynamically replace the commas with the insert statement(s), prefix & append to wrap either end, then execute dynamically. But that would require more permissions to execute. )
-
Thanks John
I definitely have in mind to move that parsing code to a function.
Brij
-
There are a few methods to do what you need, and this article discusses them and their pros and cons...
http://www.sql-server-performance.com/mm_list_random_values.asp
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Also see...
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sommarskog.se/arrays-in-sql.html
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks Ryan for providing some good links. They are good.
Brij
-
OR....
More elegantly, you can have a function which parses the values, puts them in a table and returns the table, then you can do a single insert of that table into your target table.
Even better, if you change your comma delimited input into XML, then you don't need to have the function, you can just use OPENXML, which goes like this:
EXEC sp_xml_preparedocument @handle OUTPUT, @XMLparameter
INSERT INTO CriteriaTable ([Name], [Value] )
SELECT name, value FROM OPENXML(@handle, '/productcriteria/criteria',1) WITH (name varchar(25), value varchar(50))
EXEC
sp_xml_removedocument @handle
Assuming an XML input like this:
<productcriteria>
<criteria name="x" value="1" />
<criteria name="y" value="2" />
</productcriteria>
(Handling XML input has become even more straightforward in SQL2005)
-
Hi all,
Chris - not wanting to be critical
, but more elegantly that what? Those 2 techniques are both listed, along with others, in this link http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm...But since you've brought up the subject... I must admit that I generally prefer the XML method, but the comma separated method (with a 'to table' function) seems to be a lot more common. I think some think the XML way is 'over-complicating' things, and in some cases, I guess they might be right...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Excellent, Mindblowing, Superb...............
Gr8 job dear..
-
Use Tally table to split the comma seperated values.
karthik
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply