BUG: INSERT/SELECT with Union into Table with Nullable DATETIME

• Microsoft SQL Server version 6.5

SYMPTOMS

When running an INSERT/SELECT query that contains a UNION into a table with a timestamp column that has been declared as nullable, two rows will be inserted for each row that qualifies under the select statement. One row will contain a nulled timestamp, the other will contain an actual timestamp.

WORKAROUND

To work around this problem, declare the timestamp column as NOT NULL.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

MORE INFORMATION

To reproduce this problem, run this script:

CREATE TABLE t1
(c1     INT)
go

INSERT t1
VALUES (1)
go

CREATE TABLE t2
(c1    INT,
c2     TIMESTAMP NULL)
go

INSERT INTO t2 (c1)
SELECT DISTINCT c1
FROM t1
UNION
SELECT DISTINCT c1
FROM t1 

Additional query words:

Keywords : kbSQLServ650bug
Version : winnt:6.5
Platform : winnt
Issue type : kbbug