June 18, 2003 at 10:08 am
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
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
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
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
Thanks Jpipes. Rocko
June 19, 2003 at 10:20 am
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
Thanks.Rocko
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply