Multiple insert statements in one execution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple insert statements in one execution

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-02 : 20:25:11

Hello all,

I have 3 different tables on SQL Server and my front-end app. needs to insert different data into all these 3 different tables into 1 execution. Now, I can have a stored procedure which does this. But I don't know how to make sure that everything goes in one transaction. Like if one of the insert fails, I don't need any of the data inserted into any of these tables.

Also, the other thing is: say for Table A, TAble B and Table C. With my front-end app. it needs to insert 1 row in Table A, 2 in Table B and in Table C, the number of rows will vary depending upon the data user has entered. Any idea how to do this???

I hope I wrote it clearly. Umm.. if it makes a difference, my app is written in ASP .NET and C#.

Thanks,
Ujjaval

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-02 : 20:46:46

v2000
begin tran
insert tbla
if @@error <> 0 goto err
insert tbla
if @@error <> 0 goto err
insert tbla
if @@error <> 0 goto err
commit tran
return
err:
rollback tran

v2005
begin try
begin tran
insert tbla
insert tbla
insert tbla
commit tran
end try
begin catch
rollback tran
end catch

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources