Insert Into Where NOT Exists

  • 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 🙂
  • 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 🙂

    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;

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

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

    Still getting duplicate error

  • shannonphillips - Thursday, December 28, 2017 2:27 PM

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

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

  • Ed Wagner - Thursday, December 28, 2017 1:58 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 🙂

    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.

  • Not following your references to the Red 1 char or ci1, ci2 ?

  • shannonphillips - Thursday, December 28, 2017 2:46 PM

    Not following your references to the Red 1 char or ci1, ci2 ?

    ?????

  • The "red" 1 is just a 1.  ci1 and ci2 are table aliases.

  • 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 - Thursday, December 28, 2017 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.

    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.

  • 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,

  • 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

  • Lynn Pettis - Thursday, December 28, 2017 2:36 PM

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

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

  • Lynn Pettis - Thursday, December 28, 2017 2:36 PM

    shannonphillips - Thursday, December 28, 2017 2:27 PM

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

    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