Background, I have imported dirty data with duplicate rows and bad field names into a Table called ImpTbl_ComputerInventory.
To clean the data I've created Tbl_ComputerInventory with:
CONSTRAINT [Pkey_Tbl_ComputerInventory] PRIMARY KEY CLUSTERED
(
[ComputerName] ASC,
[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]?
Now I want to copy the non duplicate rows into Tbl_ComputerInventory using a Query such as:
INSERT INTO Tbl_ComputerInventory (ComputerName, SerialNumber)
SELECT ImpTbl_ComputerInventory.[Computer Name], ImpTbl_ComputerInventory.[Service Tag]
FROM ImpTbl_ComputerInventory
Where NOT EXISTS (Select ComputerName, SerialNumber From Tbl_ComputerInventory WHERE (Tbl_ComputerInventory.ComputerName = ImpTbl_ComputerInventory.[Computer Name] AND Tbl_ComputerInventory.SerialNumber = ImpTbl_ComputerInventory.[Service Tag]));
However I'm still getting: Violation of PRIMARY KEY constraint 'Pkey_Tbl_ComputerInventory'. Cannot insert duplicate key in object 'dbo.Tbl_ComputerInventory
Thanks in advance 🙂
Try this:
INSERT INTO
[Tbl_ComputerInventory]
(
[ComputerName]
, [SerialNumber]
)
SELECT DISTINCT
[ImpTbl_ComputerInventory].[Computer Name]
, [ImpTbl_ComputerInventory].[Service Tag]
FROM
[ImpTbl_ComputerInventory];
Still getting duplicate error
Now add the WHERE clause:
INSERT INTO
[Tbl_ComputerInventory]
(
[ComputerName]
, [SerialNumber]
)
SELECT DISTINCT
[ci2].[Computer Name]
, [ci2].[Service Tag]
FROM
[ImpTbl_ComputerInventory] [ci2]
WHERE
NOT EXISTS (SELECT 1
FROM [Tbl_ComputerInventory] [ci1]
WHERE [ci2].[Computer Name] = [ci1].[Computer Name] AND [ci2].[Service Tag] = [ci1].[Service Tag]);