December 17, 2005 at 10:51 pm
How do I insert multiple records into the database from a asp.net web page. If any of the insertion fails I have to rollack the previous also.
Thanks.
December 18, 2005 at 2:35 pm
One approach would be to load the data into a working table and then insert it as a batch from that table.
If there are any error sin the insert, all the records will fail.
As I am not sure how transactional your system is, you will probably need to give the records a batch id to keep them together (not mix them up with another process).
There are probably other ways but this one popped into my head straight away.
December 19, 2005 at 6:17 am
I am having the same problem. There is some facility in SQL Server 2005 called Begin Transaction, Commit Transacion, RollBack Transaction. I am yet to study them and try them.
V.Kadal Amutham
December 19, 2005 at 11:43 am
I have encountered this problem while trying to do multiple actions against a table embedded in one transaction. I.E. Insert three records indivdually prior to the commit of the tranasaction. Only the last insert would get committed. I went Steve's way and used a table to do the inserts/updates/deletes. Worked out fine.
My issue was updating many tables ( 30+ related tables) where the requirement was to rollback all actions if any failure was encountered pior to completing all updates/inserts/deletes. I had toyed with the idea of tracking all key information for all records in all tables and segregate the transactions, but this idea was never pursued.
December 19, 2005 at 5:04 pm
The only reason I put my suggestion up was that it appeared you were coming from a web page where it would be nearly impossible to get a single oinsert transaction for multiple records - being that you have to loop through a record set.
The BEGIN, COMMIT and ROLLBACK TRAN commands already exist in SQL 2000 & before if memory serves me correctly.
In Mikes case where he has 30+ related tables these are a good option. You should actually be able to control this from within your web page code or you could use a controlling stored procedure to manage the process.
All it takes is a BEGIN TRAN at the start. Capture any errors or anomalies and either COMMIT or ROLLBACK at the end.
December 21, 2005 at 8:26 am
what do you mean by laoding the data into a working table and then insert it as a batch from that table.
Can someone give me a small sample example?
Thanks.
December 21, 2005 at 2:15 pm
If you are coming frm a web page as I suggested, you are probably processing the rows one at a time.
Pass each row to a stored procedure to insert into a table that you have created specifically for holding this data temporarily.
Once all the rows have been inserted into the working table, call another stored procedure to do the work of putting the data in the correct place in your database. This is where you get to use the transaction capabilities of SQL Server.
Two possible query options for getting then data from the working table into your database are:
-- Update any existing rows
UPDATE YourTable
SET columnlist = columnlist
FROM YourTable
INNER JOIN WorkingTable
ON WorkingTable.KeyFields = YourTable.KeyFields
-- Insert rows that are not already there
INSERT YourTable
SELECT columnlist
FROM WorkingTable
WHERE NOT EXISTS
(
SELECT 1
FROM YourTable
WHERE YourTable.KeyFields = WorkingTable.KeyFields
)
Hope this helps
January 2, 2006 at 4:12 am
u can do this with OPENXML in SQL Server 2000
StrXml = "<master field1='value' field2='20.15'>
<detail field1 ='value/>
<detail field2 = 'value'/>
</master>
CREATE PROCEDURE Usp_Test
@pStrXml text
AS
DECLARE @IntDoc INT
SP_XML_PREPAREDOCUMENT @IntDOC OUTPUT, @pStrXml
BEGIN TRANSACTION
Master Record
--------------
SELECT field1, field2
FROM OPENXML (@IntDoc,'master')
WITH (field1 varchar(30), field2 NUMERIC(12,2))
Detail Record ( Take it in CURSOR)
---------------------------------
SELECT field1
FROM OPENXML(@IntDoc,'master/detail')
After this u can loop it & UPDATE / INSERT records
IF @@ERROR <> 0
BEGIN
GOTO Error_Handler
END
COMMIT TRANSACTION
SP_XML_REMOVEDOCUMENT @IntDoc
RETURN
Error_Handler:
ROLLBACK TRANSACTION
SP_XML_REMOVEDOCUMENT @IntDoc
OPENXML Method is case sensitive
u can go any no. of master/detail nodes
This may help u out to solve your problem
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply