Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    31

    Question Unanswered: Inserting Unique Records

    Hello,

    I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.

    How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?

    Does my request make sense? Please let me know if you have questions.

    Thank you for your help!

    CSDunn

  2. #2
    Join Date
    Apr 2007
    Posts
    31
    The one way that I know to do this would be to use MAX on all but the key fields in the Select statement of the source table, and group by the key fields.

    Is there another way?

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Are you simply trying to ID (in order to eliminate) the one record that is a duplicate?

    You might try:
    Code:
    SELECT
      col1
      , col2
      , col3
      , col4
      , col5
    FROM
      dbo.MyTable
    GROUP BY
      col1
      , col2
      , col3
      , col4
      , col5
    HAVING COUNT(*) > 1
    Then copy 1 row with the duplicate data to another table (identically defined with no primary key). Delete the duplicate records from the source table and then re-import the one record from the export table.

    Otherwise, yes, you could use MAX for all but the five primary key columns to insert into your other table. Just be sure that it's MAX that you want and not MIN (or some other function).


    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Apr 2007
    Posts
    31
    Thanks for your help!

    cdun2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •