Adding several records at once

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
 
G

Guest

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

The SqlClient.SqlBulkCopy class could help.

More info and examples: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(vs.80).aspx
 
G

Guest

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
 
B

bruce barker

this will just loop thru the rows, and execute individual insert
statements. you might as well loop thru them yourself.

-- bruce (sqlwork.com)
 
B

bruce barker

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)
 
J

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
 
G

Guest

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 -

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
 
S

sloan

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top