the following transact-sql code works properly. it fills up the table with four integers ( 0, 1, 2, 3 ) as expected.
CREATE TABLE [TBL_INTEGER] (
[ID] INTEGER IDENTITY ( 1, 1 ) PRIMARY KEY,
[NUMBER] INTEGER NOT NULL
)
DECLARE @MAX INTEGER
SELECT @MAX = 1000
WHILE ( 0 < @MAX ) BEGIN
INSERT [TBL_INTEGER] ( [NUMBER] ) SELECT ABS ( CHECKSUM ( NEWID () ) ) % 4
SELECT @MAX = @MAX - 1
END
and the following code does not. it fails generating the 'Cannot insert the value NULL into column 'NUMBER' error.
CREATE TABLE [TBL_INTEGER] (
[ID] INTEGER IDENTITY ( 1, 1 ) PRIMARY KEY,
[NUMBER] INTEGER NOT NULL
)
DECLARE @MAX INTEGER
SELECT @MAX = 1000
WHILE ( 0 < @MAX ) BEGIN
INSERT [TBL_INTEGER] ( [NUMBER] )
SELECT
CASE ABS ( CHECKSUM ( NEWID () ) ) % 4
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
END
SELECT @MAX = @MAX - 1
END
if i add
ELSE 99
the code does not fail. but one third of the inserted rows contain the value 99.
is there an explanation ?
thank you in advance !
av
