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 🙂
Will something like this work for you?
WITH cteNew(Name, Serial) AS (
SELECT ComputerName, [Service Tag]
FROM dbo.ImpTbl_ComputerInventory
EXCEPT
SELECT ComputerName, SerialNumber
FROM dbo.ComputerInventory
)
INSERT INTO dbo.ComputerInventory(ComputerName, SerialNumber)
SELECT Name, Serial
FROM cteNew;