Copy Records within a Table with SQL

[y770]

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?

 

[Sylvain Lafontaine]

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]

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]

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]

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

Y770

 

[Sylvain Lafontaine]

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]

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]

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)