TSQL insert if doesn't exist
I am trying to copy 1.5M rows from one database to another. I have made many queries on SO but cannot get this to work.
The original table has duplicates (using Col1 + Col2 from the Src table) and I need to make sure that duplicates are not added to the new personnel destination. 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 composite key Col1 + Col2
DestDb.dbo.DestTable.Col1 (PK, nvarchar (128), not null)
DestDb.dbo.DestTable.Col2 (PK, datetimeoffset (7), not null)
dbo.SrcTable.Col1 (nvarchar (max), null)
dbo.SrcTable.Col2 is (datetime2 (7), not null)
I am getting this error:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint "PK_DestTable". Unable to insert duplicate key into 'dbo.DestTable' object. Duplicate key value (AAAA, 2011-10-13 00: 00: 00.0000000 +00: 00).
I use datediff
because the destination table only needs to write the date part from the source column (no temp values needed).
I am really stumped because direct insertion into the destination table using the following will work fine:
INSERT INTO [dbo].[DestTable] ([Col1], [Col2])
VALUES ('AAAA', GETDATE())
GO
source to share
You need to remove duplicates first, for example here with 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 can use SELECT DISTINCT in the second CTE instead of GROUP BY, but using SELECT DISTINCT is considered anti-pattern by many.
source to share
I hope this should work as you don't need a fraction of the time and then converting will delete it and the group will pick unique combinations of col1 and col2. Also, you wrote that col1 and col2 from DestTable are PK means that DestTable has a composite primary key (col1, col2):
insert into DestTable
select col1, cast(Convert(varchar,Col2,101) as datetimeoffset(7)) Col2
from SrcTable
group by col1, Convert(varchar,Col2,101)
source to share