Post reply

Key Items

  • June 18, 2003 at 10:08 am

    #82382

    How do you set a key in a table create that only allows the first occurance to be written to a table. I have a file that sometimes has duplicate records.

    Thanks.rocko

  • June 18, 2003 at 10:19 am

    #461942

    Not sure if I have fully understood your question, but the normal way to avoid duplicate rows is to use primary keys. The code is:

    alter table <table name>

    add constraint <object name> primary key (<column name>)

    Hope it helps.

  • June 18, 2003 at 10:35 am

    #461947

    Here is an example. I want to have two fields as primary keys. Do I just add

    Primary key as stated ?

    SELECT distinct

    TOP 100 PERCENT

    [Client ID] Primary Key,

    [Account Number] Primary Key, [Proj Black 1% Depr Month],

    [Proj Black Str Adj],

    [Proj Black Sea Adj],

    [Black Book Straight Proj Loss],

    [Black Book Seasonal Proj Loss],

    into ProjGLClient

    FROM dbo.CLNTtemp5

    ORDER BY [Client ID], [Account Number]

  • June 18, 2003 at 11:20 am

    #461960

    ROCKO, not quite sure what you're going for here...

    To set up a multi-column primary key, check BOL ALTER TABLE help article...gives examples of the ALTER TABLE ADD CONSTRAINT statement with more than onme referenced column.

    To check to see if a table has more than 1 of a key (for instance, to check if a proposed primary key is viable), run something like this:

    

    SELECT KeyColumn1, KeyColumn2, COUNT(*)

    FROM MyTable

    GROUP BY KeyColumn1, KeyColumn2

    HAVING COUNT(*) > 1

  • June 18, 2003 at 12:13 pm

    #461970

    Thanks Jpipes. Rocko

  • June 19, 2003 at 10:20 am

    #462197

    If you only need to keep the first record from a set of duplicates, you might want to create a unique index on the logical primary key fields with the 'IGNORE_DUP_KEY' index option.

    Following are details from BOL.

    IGNORE_DUP_KEY:

    Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.

    If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.

  • June 19, 2003 at 12:38 pm

    #462237

    Thanks.Rocko

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply