Adding a set of rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding a set of rows

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-07-21 : 17:41:11

Hi

I need to insert a number of rows based on a collection of ID's. The row would look like (AutoID, MyPassedID). There will never be more than 20 ID's passed, but the number will always vary.

How do I pass the collection of ID's to my SP, and then how do I loop the insert (for each passed ID, create a row).

I'm new to working with SP's, so some sample code would really help. I'm using MS SQL 2005.

Thanks as always

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-21 : 20:09:08

see
http://www.sommarskog.se/arrays-in-sql.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-22 : 06:30:05

Sommarskog's blog that Visakh pointed to is arguably one of the best resources that I have seen. It is comprehensive, but long. In your case, I would recommend two approaches, in order of preference.

1. Use XML. If your client code is .Net, it is very simple to generate the XML using LINQ to XML (see examples here: http://msdn.microsoft.com/en-us/library/bb387089.aspx ) Then, you would write a query like shown below to insert it into your table:

CREATE PROCEDURE dbo.InsertProcedure
	@xml XML
AS
INSERT INTO YourTable
(MyPassId)
SELECT
	c.query('data(.)')
FROM
	@xml.nodes('//n') T(c);

That assumes that your XML has nodes n - for example like this:

'<n>a</n>
<n>b</n>
<n>c</n>
<n>d</n>
<n>e</n>
<n />';

2. Another approach: something that is usually unsafe to do, but is OK in your case because you have ONLY numbers that you want to send in. If you have text, DON'T DO THIS, because there can be special characters (such as <, & etc.) that can cause this to fail.

CREATE PROCEDURE dbo.InsertProcedure2
	@x VARCHAR(1024)
AS
;WITH sb AS
( SELECT CAST('<n>'+REPLACE(@x,',','</n><n>')+'</n>' AS XML) AS xCol)
INSERT INTO yourTable
SELECT
	c.value('.','VARCHAR(32)') 
FROM
	sb
	CROSS APPLY xCol.nodes('//n') T(c);

You can pass in a comma separated string to this procedure, for example like this:

EXEC dbo.InsertProcedure2 'a,b,x,e,f';

Ideally, if you want to send a comma-separated string, use a string splitter such as the the one listed in Fig. 21 in this article, instead of using my unsafe shortcut approach: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources