Adding several records at once

  • Thread starter Thread starter JJ
  • Start date Start date
J

JJ

I have a need to add several userid's (for example) to a table in an sql
database. The usernames will all added to a listbox (or similar) and a
'submit' button will be clicked to send them to the database.

I have a stored procedure which adds once username at a time. I therefore
wondered what is the best way to add several?

The obvious choice seems to be to create a new stored procedure that splits
a string of user id's and adds each one inthe one procedure.
I've not done this before so wondered if I am using the right approach here.

JJ
 
I have a need to add several userid's (for example) to a table in an sql
database. The usernames will all added to a listbox (or similar) and a
'submit' button will be clicked to send them to the database.

I have a stored procedure which adds once username at a time. I therefore
wondered what is the best way to add several?

The obvious choice seems to be to create a new stored procedure that splits
a string of user id's and adds each one inthe one procedure.
I've not done this before so wondered if I am using the right approach here.

JJ
Click to expand...

The SqlClient.SqlBulkCopy class could help.

More info and examples: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(vs.80).aspx
 
You could do that, or you could add the items to a new DataTable and pass it
into a properly configured DataAdapter whose UpdateCommand points to your
sproc, and just call the Da's Update method.
Peter
 
this will just loop thru the rows, and execute individual insert
statements. you might as well loop thru them yourself.

-- bruce (sqlwork.com)
 
unlike oracle, sqlserver does not have an array insert. you can do a
batch insert, but there is no built in helper routines for this.

a batch insert would be a sql command text with an insert statement for
each user id. you can build the string looping thru your data, and
adding parameters also.

-- bruce (sqlwork.com)
 
Thanks guys.

Bruce I think you may be saying what I though originally (possibly).

That is, having a stored procedure that chops up a string of id's and loops
through adding each one.
I notice a similar approach can be seen in the stored procedure
'aspnet_UsersInRoles_AddUsersToRoles'.

Becuase it won't be huge amounts of id's in this case (but enough to
consider the consequences of repeated calls to the database for each one)
perhaps this approach looks the favourite (at the moment).

Thanks,

JJ
 
Thanks guys.

Bruce I think you may be saying what I though originally (possibly).

That is, having a stored procedure that chops up a string of id's and loops
through adding each one.
I notice a similar approach can be seen in the stored procedure
'aspnet_UsersInRoles_AddUsersToRoles'.

Becuase it won't be huge amounts of id's in this case (but enough to
consider the consequences of repeated calls to the database for each one)
perhaps this approach looks the favourite (at the moment).

Thanks,

JJ









- Show quoted text -
Click to expand...

I think you also should know that there is an xml support in SQL
Server

some examples of using xml

http://weblogs.asp.net/jgalloway/ar...s-to-sql-server-2005-with-xml-parameters.aspx
 
If you're using Sql Server, you can use xml as an input.
There is a slight performance penalty for using the OPENXML command.


Code up a strong dataset.
Add rows to the dataset programmatically.
Send the ds.GetXml() into a stored procedure.

See
http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp
for the ~~idea.

Once you have the values in a @holder table (@orders in the example), you
can do whatever you want to with it.

This also allows index rebuilding AFTER all rows are inserted. Sometimes
that alone is a performance benefit which outweights the OPENXML hit.
 
Back
Top