Seth Kramer (12/18/2010)

---

I accept I got it wrong, but what do you mean the second transaction fails? Only the insert null into the primary key field statement fails. There are no failed inserts on Table2.

Bradley Deem (12/20/2010)

---

Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.

BEGIN TRAN
 INSERT INTO [Table1]( a,b)
 VALUES(1 ,'one')

 INSERT INTO [Table1]( a,b)	-- Insert fails because the NOT NULL constraint on column [a]
 VALUES(NULL ,'five')
 -- Even though the above insert STATEMENT fails, execution of the BATCH continues.  
 -- To halt execution after error use SET XACT_ABORT = ON (stops the BATCH from executing)
 -- To reroute execution after error use TRY CATCH (recommended) or check @@Error and use GOTO
 
 INSERT INTO [Table1]( a,b)
 VALUES(4 ,'join1')

 INSERT INTO [Table1]( a,b])
 VALUES(2 ,'join3')
COMMIT TRAN
GO

volter_ddun-856145 (12/23/2010)

---

first thing it can't insert null value in to Table1 AND after running that query in sqlserver i didn't get result as mentioned Correct answer.

Correct answer: 7 rows where column d = three

can any body explain how it is possible?



thanks