handling a transaction

[fikir]

I am a bit confused here with handling a transaction

I am trying to insert data into a series of production tables.

insert into tbl1 (col1, col2)
select colt, col2 from stagingTable1

--error handling

insert into tbl2 (col1, col2
select col1, col2 from stagingTable2

--error handling

.
.
.

I want to put this in a single transaction and if the process failes any where it should rollback every thing, It should rollback to the point before the load data process starts

how can I do this.

second thing is every tables have identity columns and I want to reset these identity columns of each tables to 1
after rolling back

How do I do this also

Thanks

 

[SQLDenis]

a rollback without a save tran will roll back everything
use SET XACT_ABORT ON to make sure if you get a non trapable error

to reset identties use TRUNCATE TABLE (if you don't have FK constraints) or DBCC CHECKIDENT (TableName, RESEED, 1)


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[fikir]

Thanks Denis, that was a great help

Thanks