Hi all

I want to know that if there is any procedure to insert multiple rows in to the table of a database using stored procedure. See I am not interested to insert through a loop or using PL/SQL. just I want to know except these if there is any other procedure or not?

I found the following lines from a book that's why I am asking.


The basic INSERT statement adds one row at a time to a
table. Variations of the basic INSERT statement enable you to add multiple rows by selecting
data from another table or by executing a stored procedure.



If you know please send me the procedure.

With Regards

Somesh Chatterjee

__________________
somesh chaterjee

You can write a multi row insert like this:

INSERT INTO MyTable (field list...)
SELECT fieldlist... FROM MyOtherTable

This will add all the SELECTed rows to the table. Of course, this requires that the data already exists in another table.

Although I have never tried, I assume the same behavior applies when you use the execution of a SPROC as the "SELECT" source instead of a regular SELECT statement.

You want to add multiple records from data generated by the application, the only solution is to call an insert or sproc repeatedly. You could use a DataTable and DataAdapter to execute the update of the data (updating changed records and/or inserting new ones) but internally, I imagine this just calls the InsertCommand repeatedly.

Ultimately, there is no syntax in T-SQL that permits the passing of a row array to a single insert command apart from those described above.

-Peter