I want to insert (duplicate) a record and at the same time to change one of
the fields so it will not have key violation. Here is my SQL statement:
INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
SELECT 99999, ID2, Field1, Field2, Field3, Field4
FROM TableName WHERE ID1=2962
Primary Key in this table is composite key of ID1 and ID2. I am duplicating
a record of ID1=2962 and replacing is with 99999. There are no records for
99999. However, Access gives me error that it cannot insert 7 records because
of 7 key violations.
What do I do wrong?

It's because you hav multiple records (7) with ID1 = 2962. Remember that
this is a composite key; so using only one field it is no longer unique.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"y770" <(E-Mail Removed)> wrote in message
news:5A667A22-77AE-4BEF-93BC-(E-Mail Removed)...
>I want to insert (duplicate) a record and at the same time to change one of
> the fields so it will not have key violation. Here is my SQL statement:
> INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
> SELECT 99999, ID2, Field1, Field2, Field3, Field4
> FROM TableName WHERE ID1=2962
> Primary Key in this table is composite key of ID1 and ID2. I am
> duplicating
> a record of ID1=2962 and replacing is with 99999. There are no records for
> 99999. However, Access gives me error that it cannot insert 7 records
> because
> of 7 key violations.
> What do I do wrong?

Composite Key defined by both ID1 and ID2. Currently I have 7 records with
ID1=2962 and various ID2. I am trying to add another 7 records with ID1=99999
and same ID2 as it was for ID1=2962. There shall be no duplicate records with
ID1 and ID2 both same.
As a work around I can change the table definition: add an autonumber key
ID, remove key from ID1 and ID2, but I am afraid that my searches will be
slow, as these ID1 and ID2 are foreign keys to other tables that create
many-to-many relation. So searches by these keys are very common and frequent.
BTW, as a temp workaround I created thru VBA as query search for all records
with ID1=2962. Then in a "while" loop I am inserting one record at a time and
it works. It is not efficient way of arddessing the problem. There should be
one SQL statement that populates all records at once.
Thanks for your help.


"Sylvain Lafontaine" wrote:

> It's because you hav multiple records (7) with ID1 = 2962. Remember that
> this is a composite key; so using only one field it is no longer unique.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "y770" <(E-Mail Removed)> wrote in message
> news:5A667A22-77AE-4BEF-93BC-(E-Mail Removed)...
> >I want to insert (duplicate) a record and at the same time to change one of
> > the fields so it will not have key violation. Here is my SQL statement:
> > INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
> > SELECT 99999, ID2, Field1, Field2, Field3, Field4
> > FROM TableName WHERE ID1=2962
> > Primary Key in this table is composite key of ID1 and ID2. I am
> > duplicating
> > a record of ID1=2962 and replacing is with 99999. There are no records for
> > 99999. However, Access gives me error that it cannot insert 7 records
> > because
> > of 7 key violations.
> > What do I do wrong?
>
>
> .
>

Well, if the system is telling you that there would be 7 duplicates, than
the most logical explanation is that effectively there would be duplicates
somewhere. Either there are already records in the tmp table or you have
forgot to define a composite key for this tmp table or there is a trigger
somewhere that bring havoc.

Make a Select first on the tmp table to be sure that the records aren't
alrady there and if necessary, add an NOT EXISTS() statement.

Check for the presence of any trigger. Things that work when inserting
records one by one but don't work anymore when inserting multiple records at
once are often symptomatic of a badly constructed trigger.

For your question about speed, while a primary key is an index, there are
not the only indexes that you can install and use on a sql-server table.
Also, designing a foreign key doesn't make it an index (on SQL-Server, for
Access/JET, it's different) and making a composite key is useless when the
search is done on the second key; so you should also add an index on ID2 or
a composite index on ID2, ID1 on TableName and also on every other tables
where these indexes could be useful.

For the rest, you didn't provide the design of your tables as well as some
examples of data - in the form of Insert statements - reproducing the
problem; so I cannot tell you anything more on this.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"y770" <(E-Mail Removed)> wrote in message
news:8D298538-82D2-4B86-B080-(E-Mail Removed)...
> Composite Key defined by both ID1 and ID2. Currently I have 7 records with
> ID1=2962 and various ID2. I am trying to add another 7 records with
> ID1=99999
> and same ID2 as it was for ID1=2962. There shall be no duplicate records
> with
> ID1 and ID2 both same.
> As a work around I can change the table definition: add an autonumber key
> ID, remove key from ID1 and ID2, but I am afraid that my searches will be
> slow, as these ID1 and ID2 are foreign keys to other tables that create
> many-to-many relation. So searches by these keys are very common and
> frequent.
> BTW, as a temp workaround I created thru VBA as query search for all
> records
> with ID1=2962. Then in a "while" loop I am inserting one record at a time
> and
> it works. It is not efficient way of arddessing the problem. There should
> be
> one SQL statement that populates all records at once.
> Thanks for your help.
>
>
> "Sylvain Lafontaine" wrote:
>
>> It's because you hav multiple records (7) with ID1 = 2962. Remember that
>> this is a composite key; so using only one field it is no longer unique.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "y770" <(E-Mail Removed)> wrote in message
>> news:5A667A22-77AE-4BEF-93BC-(E-Mail Removed)...
>> >I want to insert (duplicate) a record and at the same time to change one
>> >of
>> > the fields so it will not have key violation. Here is my SQL statement:
>> > INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
>> > SELECT 99999, ID2, Field1, Field2, Field3, Field4
>> > FROM TableName WHERE ID1=2962
>> > Primary Key in this table is composite key of ID1 and ID2. I am
>> > duplicating
>> > a record of ID1=2962 and replacing is with 99999. There are no records
>> > for
>> > 99999. However, Access gives me error that it cannot insert 7 records
>> > because
>> > of 7 key violations.
>> > What do I do wrong?
>>
>>
>> .
>>

Thanks, I will look into it.
What tmp table are you refereing to?

Y770

"Sylvain Lafontaine" wrote:

> Well, if the system is telling you that there would be 7 duplicates, than
> the most logical explanation is that effectively there would be duplicates
> somewhere. Either there are already records in the tmp table or you have
> forgot to define a composite key for this tmp table or there is a trigger
> somewhere that bring havoc.
>
> Make a Select first on the tmp table to be sure that the records aren't
> alrady there and if necessary, add an NOT EXISTS() statement.
>
> Check for the presence of any trigger. Things that work when inserting
> records one by one but don't work anymore when inserting multiple records at
> once are often symptomatic of a badly constructed trigger.
>
> For your question about speed, while a primary key is an index, there are
> not the only indexes that you can install and use on a sql-server table.
> Also, designing a foreign key doesn't make it an index (on SQL-Server, for
> Access/JET, it's different) and making a composite key is useless when the
> search is done on the second key; so you should also add an index on ID2 or
> a composite index on ID2, ID1 on TableName and also on every other tables
> where these indexes could be useful.
>
> For the rest, you didn't provide the design of your tables as well as some
> examples of data - in the form of Insert statements - reproducing the
> problem; so I cannot tell you anything more on this.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "y770" <(E-Mail Removed)> wrote in message
> news:8D298538-82D2-4B86-B080-(E-Mail Removed)...
> > Composite Key defined by both ID1 and ID2. Currently I have 7 records with
> > ID1=2962 and various ID2. I am trying to add another 7 records with
> > ID1=99999
> > and same ID2 as it was for ID1=2962. There shall be no duplicate records
> > with
> > ID1 and ID2 both same.
> > As a work around I can change the table definition: add an autonumber key
> > ID, remove key from ID1 and ID2, but I am afraid that my searches will be
> > slow, as these ID1 and ID2 are foreign keys to other tables that create
> > many-to-many relation. So searches by these keys are very common and
> > frequent.
> > BTW, as a temp workaround I created thru VBA as query search for all
> > records
> > with ID1=2962. Then in a "while" loop I am inserting one record at a time
> > and
> > it works. It is not efficient way of arddessing the problem. There should
> > be
> > one SQL statement that populates all records at once.
> > Thanks for your help.
> >
> >
> > "Sylvain Lafontaine" wrote:
> >
> >> It's because you hav multiple records (7) with ID1 = 2962. Remember that
> >> this is a composite key; so using only one field it is no longer unique.
> >>
> >> --
> >> Sylvain Lafontaine, ing.
> >> MVP - Windows Live Platform
> >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> >> Independent consultant and remote programming for Access and SQL-Server
> >> (French)
> >>
> >>
> >> "y770" <(E-Mail Removed)> wrote in message
> >> news:5A667A22-77AE-4BEF-93BC-(E-Mail Removed)...
> >> >I want to insert (duplicate) a record and at the same time to change one
> >> >of
> >> > the fields so it will not have key violation. Here is my SQL statement:
> >> > INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
> >> > SELECT 99999, ID2, Field1, Field2, Field3, Field4
> >> > FROM TableName WHERE ID1=2962
> >> > Primary Key in this table is composite key of ID1 and ID2. I am
> >> > duplicating
> >> > a record of ID1=2962 and replacing is with 99999. There are no records
> >> > for
> >> > 99999. However, Access gives me error that it cannot insert 7 records
> >> > because
> >> > of 7 key violations.
> >> > What do I do wrong?
> >>
> >>
> >> .
> >>
>
>
> .
>

I'm talking about this one:

INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
SELECT 99999, ID2, Field1, Field2, Field3, Field4
FROM TableName WHERE ID1=2962


--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"y770" <(E-Mail Removed)> wrote in message
news:6C509413-BBD9-43D0-A401-(E-Mail Removed)...
> Thanks, I will look into it.
> What tmp table are you refereing to?
>
> Y770
>
> "Sylvain Lafontaine" wrote:
>
>> Well, if the system is telling you that there would be 7 duplicates, than
>> the most logical explanation is that effectively there would be
>> duplicates
>> somewhere. Either there are already records in the tmp table or you have
>> forgot to define a composite key for this tmp table or there is a trigger
>> somewhere that bring havoc.
>>
>> Make a Select first on the tmp table to be sure that the records aren't
>> alrady there and if necessary, add an NOT EXISTS() statement.
>>
>> Check for the presence of any trigger. Things that work when inserting
>> records one by one but don't work anymore when inserting multiple records
>> at
>> once are often symptomatic of a badly constructed trigger.
>>
>> For your question about speed, while a primary key is an index, there are
>> not the only indexes that you can install and use on a sql-server table.
>> Also, designing a foreign key doesn't make it an index (on SQL-Server,
>> for
>> Access/JET, it's different) and making a composite key is useless when
>> the
>> search is done on the second key; so you should also add an index on ID2
>> or
>> a composite index on ID2, ID1 on TableName and also on every other tables
>> where these indexes could be useful.
>>
>> For the rest, you didn't provide the design of your tables as well as
>> some
>> examples of data - in the form of Insert statements - reproducing the
>> problem; so I cannot tell you anything more on this.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "y770" <(E-Mail Removed)> wrote in message
>> news:8D298538-82D2-4B86-B080-(E-Mail Removed)...
>> > Composite Key defined by both ID1 and ID2. Currently I have 7 records
>> > with
>> > ID1=2962 and various ID2. I am trying to add another 7 records with
>> > ID1=99999
>> > and same ID2 as it was for ID1=2962. There shall be no duplicate
>> > records
>> > with
>> > ID1 and ID2 both same.
>> > As a work around I can change the table definition: add an autonumber
>> > key
>> > ID, remove key from ID1 and ID2, but I am afraid that my searches will
>> > be
>> > slow, as these ID1 and ID2 are foreign keys to other tables that create
>> > many-to-many relation. So searches by these keys are very common and
>> > frequent.
>> > BTW, as a temp workaround I created thru VBA as query search for all
>> > records
>> > with ID1=2962. Then in a "while" loop I am inserting one record at a
>> > time
>> > and
>> > it works. It is not efficient way of arddessing the problem. There
>> > should
>> > be
>> > one SQL statement that populates all records at once.
>> > Thanks for your help.
>> >
>> >
>> > "Sylvain Lafontaine" wrote:
>> >
>> >> It's because you hav multiple records (7) with ID1 = 2962. Remember
>> >> that
>> >> this is a composite key; so using only one field it is no longer
>> >> unique.
>> >>
>> >> --
>> >> Sylvain Lafontaine, ing.
>> >> MVP - Windows Live Platform
>> >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> >> Independent consultant and remote programming for Access and
>> >> SQL-Server
>> >> (French)
>> >>
>> >>
>> >> "y770" <(E-Mail Removed)> wrote in message
>> >> news:5A667A22-77AE-4BEF-93BC-(E-Mail Removed)...
>> >> >I want to insert (duplicate) a record and at the same time to change
>> >> >one
>> >> >of
>> >> > the fields so it will not have key violation. Here is my SQL
>> >> > statement:
>> >> > INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
>> >> > SELECT 99999, ID2, Field1, Field2, Field3, Field4
>> >> > FROM TableName WHERE ID1=2962
>> >> > Primary Key in this table is composite key of ID1 and ID2. I am
>> >> > duplicating
>> >> > a record of ID1=2962 and replacing is with 99999. There are no
>> >> > records
>> >> > for
>> >> > 99999. However, Access gives me error that it cannot insert 7
>> >> > records
>> >> > because
>> >> > of 7 key violations.
>> >> > What do I do wrong?
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

Now I see why I could not understand you (or you mee?)
That was a typo. tmp is supposed to be TableName. I am actually trying to
copy records within same table. Does this make more sence?
I am sorry!

"Sylvain Lafontaine" wrote:

> I'm talking about this one:
>
> INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
> SELECT 99999, ID2, Field1, Field2, Field3, Field4
> FROM TableName WHERE ID1=2962
>
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "y770" <(E-Mail Removed)> wrote in message
> news:6C509413-BBD9-43D0-A401-(E-Mail Removed)...
> > Thanks, I will look into it.
> > What tmp table are you refereing to?
> >
> > Y770
> >
> > "Sylvain Lafontaine" wrote:
> >
> >> Well, if the system is telling you that there would be 7 duplicates, than
> >> the most logical explanation is that effectively there would be
> >> duplicates
> >> somewhere. Either there are already records in the tmp table or you have
> >> forgot to define a composite key for this tmp table or there is a trigger
> >> somewhere that bring havoc.
> >>
> >> Make a Select first on the tmp table to be sure that the records aren't
> >> alrady there and if necessary, add an NOT EXISTS() statement.
> >>
> >> Check for the presence of any trigger. Things that work when inserting
> >> records one by one but don't work anymore when inserting multiple records
> >> at
> >> once are often symptomatic of a badly constructed trigger.
> >>
> >> For your question about speed, while a primary key is an index, there are
> >> not the only indexes that you can install and use on a sql-server table.
> >> Also, designing a foreign key doesn't make it an index (on SQL-Server,
> >> for
> >> Access/JET, it's different) and making a composite key is useless when
> >> the
> >> search is done on the second key; so you should also add an index on ID2
> >> or
> >> a composite index on ID2, ID1 on TableName and also on every other tables
> >> where these indexes could be useful.
> >>
> >> For the rest, you didn't provide the design of your tables as well as
> >> some
> >> examples of data - in the form of Insert statements - reproducing the
> >> problem; so I cannot tell you anything more on this.
> >>
> >> --
> >> Sylvain Lafontaine, ing.
> >> MVP - Windows Live Platform
> >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> >> Independent consultant and remote programming for Access and SQL-Server
> >> (French)
> >>
> >>
> >> "y770" <(E-Mail Removed)> wrote in message
> >> news:8D298538-82D2-4B86-B080-(E-Mail Removed)...
> >> > Composite Key defined by both ID1 and ID2. Currently I have 7 records
> >> > with
> >> > ID1=2962 and various ID2. I am trying to add another 7 records with
> >> > ID1=99999
> >> > and same ID2 as it was for ID1=2962. There shall be no duplicate
> >> > records
> >> > with
> >> > ID1 and ID2 both same.
> >> > As a work around I can change the table definition: add an autonumber
> >> > key
> >> > ID, remove key from ID1 and ID2, but I am afraid that my searches will
> >> > be
> >> > slow, as these ID1 and ID2 are foreign keys to other tables that create
> >> > many-to-many relation. So searches by these keys are very common and
> >> > frequent.
> >> > BTW, as a temp workaround I created thru VBA as query search for all
> >> > records
> >> > with ID1=2962. Then in a "while" loop I am inserting one record at a
> >> > time
> >> > and
> >> > it works. It is not efficient way of arddessing the problem. There
> >> > should
> >> > be
> >> > one SQL statement that populates all records at once.
> >> > Thanks for your help.
> >> >
> >> >
> >> > "Sylvain Lafontaine" wrote:
> >> >
> >> >> It's because you hav multiple records (7) with ID1 = 2962. Remember
> >> >> that
> >> >> this is a composite key; so using only one field it is no longer
> >> >> unique.
> >> >>
> >> >> --
> >> >> Sylvain Lafontaine, ing.
> >> >> MVP - Windows Live Platform
> >> >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> >> >> Independent consultant and remote programming for Access and
> >> >> SQL-Server
> >> >> (French)
> >> >>
> >> >>
> >> >> "y770" <(E-Mail Removed)> wrote in message
> >> >> news:5A667A22-77AE-4BEF-93BC-(E-Mail Removed)...
> >> >> >I want to insert (duplicate) a record and at the same time to change
> >> >> >one
> >> >> >of
> >> >> > the fields so it will not have key violation. Here is my SQL
> >> >> > statement:
> >> >> > INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
> >> >> > SELECT 99999, ID2, Field1, Field2, Field3, Field4
> >> >> > FROM TableName WHERE ID1=2962
> >> >> > Primary Key in this table is composite key of ID1 and ID2. I am
> >> >> > duplicating
> >> >> > a record of ID1=2962 and replacing is with 99999. There are no
> >> >> > records
> >> >> > for
> >> >> > 99999. However, Access gives me error that it cannot insert 7
> >> >> > records
> >> >> > because
> >> >> > of 7 key violations.
> >> >> > What do I do wrong?
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>

Coying into the same table shouldn't change anything. Are you sure that
there is no existing previous records already there with (99999, ID2) ?
What version of SQL-Server are you using? What happens if you add an NOT
EXISTS () statement? Is the primary key clustered or not?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"y770" <(E-Mail Removed)> wrote in message
news:3FC6CE08-A93E-46DE-8A6C-(E-Mail Removed)...
> Now I see why I could not understand you (or you mee?)
> That was a typo. tmp is supposed to be TableName. I am actually trying to
> copy records within same table. Does this make more sence?
> I am sorry!
>
> "Sylvain Lafontaine" wrote:
>
>> I'm talking about this one:
>>
>> INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
>> SELECT 99999, ID2, Field1, Field2, Field3, Field4
>> FROM TableName WHERE ID1=2962
>>
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "y770" <(E-Mail Removed)> wrote in message
>> news:6C509413-BBD9-43D0-A401-(E-Mail Removed)...
>> > Thanks, I will look into it.
>> > What tmp table are you refereing to?
>> >
>> > Y770
>> >
>> > "Sylvain Lafontaine" wrote:
>> >
>> >> Well, if the system is telling you that there would be 7 duplicates,
>> >> than
>> >> the most logical explanation is that effectively there would be
>> >> duplicates
>> >> somewhere. Either there are already records in the tmp table or you
>> >> have
>> >> forgot to define a composite key for this tmp table or there is a
>> >> trigger
>> >> somewhere that bring havoc.
>> >>
>> >> Make a Select first on the tmp table to be sure that the records
>> >> aren't
>> >> alrady there and if necessary, add an NOT EXISTS() statement.
>> >>
>> >> Check for the presence of any trigger. Things that work when
>> >> inserting
>> >> records one by one but don't work anymore when inserting multiple
>> >> records
>> >> at
>> >> once are often symptomatic of a badly constructed trigger.
>> >>
>> >> For your question about speed, while a primary key is an index, there
>> >> are
>> >> not the only indexes that you can install and use on a sql-server
>> >> table.
>> >> Also, designing a foreign key doesn't make it an index (on SQL-Server,
>> >> for
>> >> Access/JET, it's different) and making a composite key is useless when
>> >> the
>> >> search is done on the second key; so you should also add an index on
>> >> ID2
>> >> or
>> >> a composite index on ID2, ID1 on TableName and also on every other
>> >> tables
>> >> where these indexes could be useful.
>> >>
>> >> For the rest, you didn't provide the design of your tables as well as
>> >> some
>> >> examples of data - in the form of Insert statements - reproducing the
>> >> problem; so I cannot tell you anything more on this.
>> >>
>> >> --
>> >> Sylvain Lafontaine, ing.
>> >> MVP - Windows Live Platform
>> >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> >> Independent consultant and remote programming for Access and
>> >> SQL-Server
>> >> (French)
>> >>
>> >>
>> >> "y770" <(E-Mail Removed)> wrote in message
>> >> news:8D298538-82D2-4B86-B080-(E-Mail Removed)...
>> >> > Composite Key defined by both ID1 and ID2. Currently I have 7
>> >> > records
>> >> > with
>> >> > ID1=2962 and various ID2. I am trying to add another 7 records with
>> >> > ID1=99999
>> >> > and same ID2 as it was for ID1=2962. There shall be no duplicate
>> >> > records
>> >> > with
>> >> > ID1 and ID2 both same.
>> >> > As a work around I can change the table definition: add an
>> >> > autonumber
>> >> > key
>> >> > ID, remove key from ID1 and ID2, but I am afraid that my searches
>> >> > will
>> >> > be
>> >> > slow, as these ID1 and ID2 are foreign keys to other tables that
>> >> > create
>> >> > many-to-many relation. So searches by these keys are very common and
>> >> > frequent.
>> >> > BTW, as a temp workaround I created thru VBA as query search for all
>> >> > records
>> >> > with ID1=2962. Then in a "while" loop I am inserting one record at a
>> >> > time
>> >> > and
>> >> > it works. It is not efficient way of arddessing the problem. There
>> >> > should
>> >> > be
>> >> > one SQL statement that populates all records at once.
>> >> > Thanks for your help.
>> >> >
>> >> >
>> >> > "Sylvain Lafontaine" wrote:
>> >> >
>> >> >> It's because you hav multiple records (7) with ID1 = 2962.
>> >> >> Remember
>> >> >> that
>> >> >> this is a composite key; so using only one field it is no longer
>> >> >> unique.
>> >> >>
>> >> >> --
>> >> >> Sylvain Lafontaine, ing.
>> >> >> MVP - Windows Live Platform
>> >> >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> >> >> Independent consultant and remote programming for Access and
>> >> >> SQL-Server
>> >> >> (French)
>> >> >>
>> >> >>
>> >> >> "y770" <(E-Mail Removed)> wrote in message
>> >> >> news:5A667A22-77AE-4BEF-93BC-(E-Mail Removed)...
>> >> >> >I want to insert (duplicate) a record and at the same time to
>> >> >> >change
>> >> >> >one
>> >> >> >of
>> >> >> > the fields so it will not have key violation. Here is my SQL
>> >> >> > statement:
>> >> >> > INSERT INTO tmp (ID1, ID2, Field1, Field2, Field3, Field4)
>> >> >> > SELECT 99999, ID2, Field1, Field2, Field3, Field4
>> >> >> > FROM TableName WHERE ID1=2962
>> >> >> > Primary Key in this table is composite key of ID1 and ID2. I am
>> >> >> > duplicating
>> >> >> > a record of ID1=2962 and replacing is with 99999. There are no
>> >> >> > records
>> >> >> > for
>> >> >> > 99999. However, Access gives me error that it cannot insert 7
>> >> >> > records
>> >> >> > because
>> >> >> > of 7 key violations.
>> >> >> > What do I do wrong?
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>