[SOLVED] Populating GUID column with NEWID() in an INSERT INTO SELECT FROM statement is throwing an error (NULL) value

Populating GUID column with NEWID() in an INSERT INTO SELECT FROM statement is throwing an error (NULL) value

I am trying to populate Table1 with values from Table2 if they do not already exist in Table1.

Table1 has a UNIQUEIDENTIFIER constraint and has a default value of NEWID() specified.

I was using a SELECT DISTINCT statement to get the results from Table2 and eventually just created a view to do that for me. I'll state up front that there are no duplicate values in Table2View.

I've also tried to drop the results of the SELECT statement to a temp table and populating a GUID on the temp table using NEWID() and pulling the values over from there. No matter what I've tried so far produces the same error.

Violation of UNIQUE KEY constraint 'constraintname'. Cannot insert duplicate key in object 'dbo.table1'. The duplicate key value is NULL.

Here is the CREATE TO on Table1:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1]
(
    [UID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [DataID] [varchar](10) NULL,
    [DataName] [varchar](100) NULL,
    [DataType] [varchar](10) NULL,
    [DateLastPopulated] [date] NULL,
    [LastAmount] [bigint] NULL,
    [DateRecordCreated] [datetime] NULL,
    [DateLastModified] [datetime] NULL,

    PRIMARY KEY CLUSTERED ([UID] ASC) 
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    UNIQUE NONCLUSTERED ([DataID] ASC) 
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] 
    ADD CONSTRAINT [DF_Table1_UID] DEFAULT (NEWID()) FOR [UID]
GO

ALTER TABLE [dbo].[Table1] 
    ADD CONSTRAINT [CNST_Table1_CreateDate] DEFAULT (GETDATE()) FOR [DateRecordCreated]
GO

ALTER TABLE [dbo].[Table1]  WITH CHECK 
    ADD CONSTRAINT [FK_Table1_ForeignTable] 
        FOREIGN KEY([ForeignID]) REFERENCES [dbo].[ForeignTable] ([ForeignID])
GO

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_ForeignTable]
GO

Here is the query I am running to populate the table:

INSERT INTO [dbo].[Table1] ([UID], DataName, DateLastPopulated, LastAmount)
    SELECT NEWID(), B.DataName, B.DateLastPopulated, B.LastAmount
    FROM [dbo].[Table2_View] B
    LEFT JOIN [dbo].[Table1] A ON B.DataName = A.DataName
    WHERE A.DataName IS NULL

I have ran the SELECT statement from the INSERT by itself and there are no NULL values and no duplicate values. The query runs fine and NEWID() populates a GUID.

However, adding the SELECT statement to the INSERT INTO produces the error.

Any help at all would be greatly appreciated. I've googled this issue to the point of insanity.

Solution

Well coincidentally, I was able to solve the problem. I removed the Primary Key from the GUID column and the query ran just fine after that. It seems that it was trying to commit the NULL value before committing the full record. Doing a SELECT * on the table after running the query shows that there were no NULL values inserted. Good grief!