BOL: Transactions involving table variables last only for the duration of an update on the table variable. Therefore...
Hi all!
Maybe the following code example brings some light on the above statement. Note the second insert in the try block.
Try guessing the outcome...
SET NOCOUNT ON
GO
DECLARE @t TABLE (id_char VARCHAR(20))
CREATE TABLE #t (id INT)
BEGIN TRY
BEGIN TRAN
-- insert 1 into temp table and output to table var
INSERT #t
OUTPUT INSERTED.*
INTO @t
SELECT 1
-- Now insert 'A' into table var and output to temp table
-- NOTE: 'A' can not be inserted into #t !
INSERT INTO @t
OUTPUT INSERTED.*
INTO #t
SELECT 'A'
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() >0 BEGIN
PRINT 'COMMIT transaction'
PRINT ERROR_MESSAGE()
COMMIT TRAN
END
ELSE BEGIN
IF XACT_STATE() < 0 BEGIN
PRINT 'ROLLBACK transaction'
ROLLBACK
END
PRINT ERROR_MESSAGE()
END
END CATCH
-- display unioned results
SELECT '@t' AS 'table_name', COUNT(*) AS 'row_count' FROM @t
UNION ALL
SELECT '#t' AS 'table_name', COUNT(*) AS 'row_count' FROM #t
-- clean up
DROP TABLE #t
Regards,
Hrvoje Piasevoli
Hrvoje Piasevoli