Lynn Pettis - Thursday, December 28, 2017 1:59 PM

shannonphillips - Thursday, December 28, 2017 1:32 PM

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];