December 28, 2017 at 1:32 pm
December 28, 2017 at 1:58 pm
shannonphillips - Thursday, December 28, 2017 1:32 PMBackground, 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_ComputerInventoryThanks 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;
December 28, 2017 at 1:59 pm
shannonphillips - Thursday, December 28, 2017 1:32 PMBackground, 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_ComputerInventoryThanks in advance 🙂
Try this:
INSERT INTO
[Tbl_ComputerInventory]
(
[ComputerName]
, [SerialNumber]
)
SELECT DISTINCT
[ImpTbl_ComputerInventory].[Computer Name]
, [ImpTbl_ComputerInventory].[Service Tag]
FROM
[ImpTbl_ComputerInventory];
December 28, 2017 at 2:27 pm
Lynn Pettis - Thursday, December 28, 2017 1:59 PMshannonphillips - Thursday, December 28, 2017 1:32 PMBackground, 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_ComputerInventoryThanks 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
December 28, 2017 at 2:36 pm
shannonphillips - Thursday, December 28, 2017 2:27 PMLynn Pettis - Thursday, December 28, 2017 1:59 PMshannonphillips - Thursday, December 28, 2017 1:32 PMBackground, 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_ComputerInventoryThanks 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]);
December 28, 2017 at 2:41 pm
Ed Wagner - Thursday, December 28, 2017 1:58 PMshannonphillips - Thursday, December 28, 2017 1:32 PMBackground, 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_ComputerInventoryThanks 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;
gives me duplicate errors as well.
December 28, 2017 at 2:46 pm
Not following your references to the Red 1 char or ci1, ci2 ?
December 28, 2017 at 2:46 pm
shannonphillips - Thursday, December 28, 2017 2:46 PMNot following your references to the Red 1 char or ci1, ci2 ?
?????
December 28, 2017 at 2:47 pm
The "red" 1 is just a 1. ci1 and ci2 are table aliases.
December 28, 2017 at 4:13 pm
Can you post the table definitions including collation for character columns?
If your import table has a case-sensitive collation and your destination table does not you could still get duplicates with all the solutions provided already.
Fixed-length vs. variable length could potentially be an issue here as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 29, 2017 at 6:54 am
Jack Corbett - Thursday, December 28, 2017 4:13 PMCan you post the table definitions including collation for character columns?If your import table has a case-sensitive collation and your destination table does not you could still get duplicates with all the solutions provided already.
Fixed-length vs. variable length could potentially be an issue here as well.
Good point, Jack. I thought about asking for the table DDL before I posted yesterday, but skipped it because it seemed easy enough. Didn't think of collation.
Shannon, the table definitions are the full CREATE TABLE statements.
December 29, 2017 at 7:03 am
Looking at the Extended Properties Both say Collation= SQL_Latin1_General_CP1_CI_AS
And the field types are the same..
CREATE TABLE [dbo].[ImpTbl_ComputerInventory](
[Computer Name] [nvarchar](15) NOT NULL,
[Service Tag] [nvarchar](35) NOT NULL,
CREATE TABLE [dbo].[Tbl_ComputerInventory](
[ComputerName] [nvarchar](15) NOT NULL,
[SerialNumber] [nvarchar](35) NOT NULL,
December 29, 2017 at 7:41 am
Those aren't the full definitions - you haven't included primary keys or any other constraints. Since the error is a primary key violation, it's important to know something about the primary key.
Edit - ignore that - I read your last post properly, but not your first!
Edit - Lynn's code should indeed work - checking duplicates and existing values. I'm intrigued, though - why is your primary key on the combination of computer name and serial number? Surely each of those attributes should be unique in its own right?
John
December 29, 2017 at 7:42 am
Lynn Pettis - Thursday, December 28, 2017 2:36 PMNow 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]);
Have you tried this code from Lynn? This should work. Another option is to use ROW_NUMBER(), something like this:
WITH UniqueCI AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY
ITCI.[Computer Name],
ITCI.[Service Tag]
ORDER BY
ITCI.[Computer Name]
) AS RowNo,
ITCI.[Computer Name],
ITCI.[Service Tag]
FROM
dbo.ImpTbl_ComputerInventory AS ITCI
)
INSERT INTO dbo.Tbl_ComputerInventory (ComputerName, SerialNumber)
SELECT
*
FROM
UniqueCI AS UCI
WHERE
UCI.RowNo = 1 AND
NOT EXISTS
(
SELECT
1
FROM
dbo.Tbl_ComputerInventory AS ITCI
WHERE
UCI.[Computer Name] = ITCI.ComputerName AND
UCI.[Service Tag] = ITCI.SerialNumber
);
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 29, 2017 at 9:14 am
Lynn Pettis - Thursday, December 28, 2017 2:36 PMshannonphillips - Thursday, December 28, 2017 2:27 PMLynn Pettis - Thursday, December 28, 2017 1:59 PMshannonphillips - Thursday, December 28, 2017 1:32 PMBackground, 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_ComputerInventoryThanks 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]);
So, have you tried the code I post above?
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply