I need to know if the T-SQL below is correct to roll back if something bad happens.
I first create a temp table
IF OBJECT_ID(N'tempdb..#LocalSummary') IS NOT NULL DROP TABLE #LocalSummary CREATE TABLE #LocalSummary (
then I populate the table
INSERT INTO #LocalSummary SELECT *....
Then I start my try catch
BEGIN TRY BEGIN TRANSACTION DELETE FROM LocalSummaryRealTable INSERT INTO LocalSummaryRealTable SELECT S.* FROM #LocalSummary AS S COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION ...
I noticed when I did the insert into the
LocalSummaryRealTable the columns didn't line up and I got an error. I expected things to roll back but in SSMS query window it gave me a message that the trans wasn't committed and asked me if I wanted to commit it. Why would this ask me when it should of been rolled back?