Skip-over/ignore duplicate rows on insert
You could assign the PK as Ignore Duplicate Key = Yes. Then it will just give a warning duplicate key ignored and continue. I am not guessing. I tested this.
What I found is that I cannot do this is SMSS. Have to drop and recreate the index via script. But you can right click on the index, select drop and recreate, and then just change Ignore Duplicate Key = Yes. For me SMSS did not immediately show the change.
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PKallowDup]') AND name = N'PK_PKallowDup')
ALTER TABLE [dbo].[PKallowDup] DROP CONSTRAINT [PK_PKallowDup]
GO
USE [test]
GO
/****** Object: Index [PK_PKallowDup] Script Date: 05/22/2012 10:23:13 ******/
ALTER TABLE [dbo].[PKallowDup] ADD CONSTRAINT [PK_PKallowDup] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Or I think you could use an outer join
INSERT dbo.DataValue(DateStamp, ItemId, Value)
SELECT t.DateStamp, t.ItemId, t.Value
FROM dbo.tmp_holding_DataValue AS t
left join dbo.DataValue AS d
on d.DateStamp = t.DateStamp
AND d.ItemId = t.ItemId
WHERE d.DateStamp is null
and d.ItemId in null
INSERT dbo.DataValue(DateStamp, ItemId, Value)
SELECT DateStamp, ItemId, Value
FROM dbo.tmp_holding_DataValue AS t
WHERE NOT EXISTS (SELECT 1 FROM dbo.DataValue AS d
WHERE DateStamp = t.DateStamp
AND ItemId = t.ItemId);