>I keep getting a violation of unique key constaint 'StudemoU21' cannot insert duplicate key in object studemo.

You're trying to insert a combination of data items that already exists, as determined by your unique constraint StudemoU21. You'll have to get the columns that the constraint is being check against to figure out where the conflict is occuring.

Am I missing something here?

-Mike

>
>There are three keys one on field suniq the primary key and unique keys on the ident and stuuniq fields my sql is
>
>INSERT INTO [studemo]
>SELECT cisStudemo.*
>FROM cisStudemo
>INNER JOIN studemo
>ON
>cisStudemo.suniq != studemo.suniq
>AND
> cisStudemo.ident != studemo.ident
>AND
> cisStudemo.stuuniq != studemo.stuuniq
>
>I also tried this with all of the field names listed and that got the same violation.
>
>Thanks
>Jim
>
>
>
>
>>you've lost me. what about the PK constraint is giving you problems? Are you getting conflicts? Is the PK an identity and you want to assign the value? (Check SET IDENTITY_INSERT in BOL).
>>
>>>What are the
>>>_WA_Sys_homeaddr1_6FB49575 nonclustered, statistics, auto create located on PRIMARY homeaddr1
>>>Indexes?
>>
>>Column statistics that were created by the Query Optimizer.
>>
>>-Mike
>>
>>
>>
>>>Hi Michael,
>>>
>>>Thanks for your reply here is the pertinent part of the exec sp_helpindex studemo
>>>
>>>index_name index_description index_keys
>>>------------- ------------------------------------------------- --------------
>>>studemoPK clustered, unique, primary key located on PRIMARY suniq
>>>studemI01 nonclustered located on PRIMARY genderc
>>>studemI02 nonclustered located on PRIMARY ethnicc
>>>studemI03 nonclustered located on PRIMARY homelangc
>>>studemI04 nonclustered located on PRIMARY primlangc
>>>studemI05 nonclustered located on PRIMARY bverbasc
>>>studemI06 nonclustered located on PRIMARY citizenc
>>>studemI07 nonclustered located on PRIMARY countryc
>>>studemI08 nonclustered located on PRIMARY resdistc
>>>studemI09 nonclustered located on PRIMARY gradreqc
>>>studemI10 nonclustered located on PRIMARY careeruniq
>>>studemI11 nonclustered located on PRIMARY hlduniq
>>>studemI12 nonclustered located on PRIMARY buspuniq
>>>studemI13 nonclustered located on PRIMARY busduniq
>>>studemI14 nonclustered located on PRIMARY maritalc
>>>studemI15 nonclustered located on PRIMARY inforelc
>>>studemI16 nonclustered located on PRIMARY memberc
>>>studemI17 nonclustered located on PRIMARY geocode
>>>studemI18 nonclustered located on PRIMARY counsfuniq
>>>studemI19 nonclustered located on PRIMARY suniq, ident
>>>studemI20 nonclustered located on PRIMARY lastname, firs
>>>studemI21 nonclustered located on PRIMARY suniq, ident,
>>>studemU1 nonclustered, unique located on PRIMARY ident
>>>studemU2 nonclustered, unique located on PRIMARY stuuniq
>>>
>>>
>>>The only thing I would have to worry about would be studemoPK (the first one) which is clustered, unique, primary key located on Primary and the two studemU1 nonclustered, unique located on PRIMARY ident
>>>studemU2 nonclustered, unique located on PRIMARY stuuniq
>>>
>>>
>>>What are the
>>>_WA_Sys_homeaddr1_6FB49575 nonclustered, statistics, auto create located on PRIMARY homeaddr1
>>>Indexes?
>>>
>>>Thanks a lot
>>>Jim
>>>
>>>
>>>
>>>
>>>>The INSERT SELECT is probably your best choice. You might find it easier if you explicitly list the columns instead of relying on SQL Server to guess.
>>>>
>>>>>> I am having problems with the constraints on the ident field and others.
>>>>
>>>>You'll have to provide a little more info.
>>>>
>>>>-Mike