TSQL insert if not exists

Question

I'm trying to copy 1.5M rows from one database to another database. I've done many searches on SO but can't get this to work.

The source table has duplicates (using Col1 + Col2 from Src table) and I need to ensure no duplicates are inserted into the new destination tabe. This is the SQL I have:

INSERT INTO DestDb.dbo.DestTable ([Col1], [Col2])   
SELECT [Col1], DATEADD(dd, DATEDIFF(dd, 0, [Col2]), 0)  
FROM dbo.SrcTable as Table1
WHERE NOT EXISTS (
                    SELECT 1
                    FROM DestDb.dbo.DestTable
                    WHERE DestDb.dbo.DestTable.Col1 = Table1.Col1
                        AND DATEDIFF(DAY, DestDb.dbo.DestTable.Col2, Table1.Col2) = 0
)

DestDb.dbo.DestTable has a composite key of Col1 + Col2

DestDb.dbo.DestTable.Col1 is (PK, nvarchar(128), not null)

DestDb.dbo.DestTable.Col2 is (PK, datetimeoffset(7), not null)

dbo.SrcTable.Col1 is (nvarchar(max), null)

dbo.SrcTable.Col2 is (datetime2(7), not null)

I get this error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_DestTable'. Cannot insert duplicate key in object 'dbo.DestTable'. The duplicate key value is (AAAA, 2011-10-13 00:00:00.0000000 +00:00).

I'm using datediff because the destination table only needs to record the date portion from the source column (no time values needed).

I'm really stumped because a direct insert into the destination table using the following will work fine:

INSERT INTO [dbo].[DestTable] ([Col1], [Col2])
VALUES ('AAAA', GETDATE())
GO

Answer 1

You need to eliminate duplicates first, such as here with a GROUP BY:

with
source as (
    SELECT [Col1], DATEADD(dd, DATEDIFF(dd, 0, [Col2]), 0)  as Col2
    FROM dbo.SrcTable as Table1
),
data as (
    select Col1,Col2 from source group by Col1,Col2 
)
INSERT INTO DestDb.dbo.DestTable ([Col1], [Col2])   
SELECT Col1,Col2 FROM data
;

You could use SELECT DISTINCT in the second CTE instead of the GROUP BY, but the use of SELECT DISTINCT is considered an anti-pattern by many.