Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert Multiple rows using Transacrtions Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 9:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 10:27 PM
Points: 35, Visits: 39
Hi..

I have a table and need insert multiple data into it. First i have delete all data into the table and then insert multiple rows in table using sql transactions so that if any error comes all data will not be deleted.

Thanks and Regards

Sankar
Cochin
Post #1468764
Posted Saturday, June 29, 2013 3:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
sankar.nandakumaran (6/28/2013)
Hi..

I have a table and need insert multiple data into it. First i have delete all data into the table and then insert multiple rows in table using sql transactions so that if any error comes all data will not be deleted.

Thanks and Regards

Sankar
Cochin

The quick answer for >SQL2008 would be something like:


BEGIN TRAN

[MERGE with INSERT. UPDATE, and DELETE]

END TRAN


Check out these articles:

Using the MERGE Statement

Merge error handling

 
Post #1468781
Posted Saturday, June 29, 2013 5:12 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
For a single command (MERGE is a single command) you do not need "begin tran",
each command starts implicit transaction, and commits/rollback implicitly, all the time (if not within larger transaction).
Also, there is no "end tran" command, you probably meant "commit tran".


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1468831
Posted Sunday, June 30, 2013 9:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Vedran Kesegic (6/29/2013)
Also, there is no "end tran" command, you probably meant "commit tran".

You are correct about COMMIT TRAN. It was late after a long day!

 
Post #1468866
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse