BUG: INSERT/SELECT with Union into Table with Nullable DATETIME (223403)



The information in this article applies to:

  • Microsoft SQL Server 6.5

This article was previously published under Q223403
BUG #: 18664 (SQLBUG_65)

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 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
				

Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB223403