Append query.

A

alfranco17

New member

Local time

Yesterday, 22:42

Joined

Oct 3, 2012

Messages

3

• Oct 3, 2012

• #1

Hi.​

I'm trying to run an append query to copy from a table to its twin.

It works fine the first time I run it, when t_transactions is empty, but fails when it has even a single record and I get a "key violations error" .
​

I am not including the primary key on the query, and believe it should be automatically assigned (it is an autonumber).


INSERT INTO t_transactions ( id_trans_type, id_product, f_quantity, id_locación, effdate, actdate, id_user, id_po, id_so, id_inv, f_comments )
SELECT 'TRANSFER' AS f_trans_type, temp_t_transactions.id_product, temp_t_transactions.f_quantity, temp_t_transactions.id_locación, temp_t_transactions.effdate, Now() AS actdate, 'AFRANCO' AS id_user, temp_t_transactions.id_po, temp_t_transactions.id_so, temp_t_transactions.id_inv, temp_t_transactions.f_comments
FROM temp_t_transactions;

Any help will be greatly appreciated, as I have no idea what could be wrong. If I type in the data, it works. It works the first time also, but when there are records in the destination, there is always a key conflict.

Thanks.
Armando.​

 

A

alfranco17

New member

Local time

Yesterday, 22:42

Joined

Oct 3, 2012

Messages

3

• Oct 3, 2012

• #2

Solved! I had to "Compact and repair" the database.

Thanks.

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 15:42

Joined

Jan 20, 2009

Messages

12,895

• Oct 3, 2012

• #3

Excellent.

It sounds like the autnumber seed had corrupted. Tables can be prone to this when records have been appended including the autonumber field. It will often start again from the appended autonumber.

Sometimes a C&R can fix it but sometimes more steps are required.

 

A

alfranco17

New member

Local time

Yesterday, 22:42

Joined

Oct 3, 2012

Messages

3

• Oct 3, 2012

• #4

Thanks Galaxiom! Now I know the name of the problem.