[Solved] Inserting records to avoid AutoValue ID conflict

[Nocton]

I have a table, 'Matches', with details of matches played over several years - the two key fields for the problem I have are 'MatchID' and 'DatePlayed'. 'MatchID' is the Primary Key which is auto-incremented. When a new season/year starts it would be convenient to copy last year's match details to a new year, e.g. all dates for 2017 of type dd/mm/17 would be copied and the date changed to dd/mm/18.

I have successfully:
- Extracted the required data from 'Matches' into a temporary file called 'Temp'
- Updated the year in the 'DatePlayed' field from 17 to 18.

Now I need to insert the records from 'Temp' into 'Matches'. However when I do this with an INSERT FROM statement, the 'MatchID' values in 'Temp', copied from 'Matches', cause a conflict because they are identical.

The question is how can I insert the records from 'Temp' while allowing 'MatchID' to update automatically as it would if a new record were added manually or via a form?

I tried setting all the MatchIDs in 'Temp' to Null, but that did not solve the problem.

[UnklDonald418]

If "MatchID" in the table "Matches" is auto-incrementing then don't copy "MatchID" from "Temp"

Code: Select all

INSERT INTO "Matches" SELECT "DatePlayed" FROM "Temp" 

Should append all the records in "Temp" to "Matches"

[chrisb]

Nocton,
no need to select or insert the primary key field, that takes care of itself.
we don't have your field names but it's important to note that the field positions contained in the select & insert clauses must correspond i.e. first field in select clause is saved to first field in insert clause etc.

Code: Select all

insert into "Matches"("DatePlayed", "NameOfFieldWhereColumn2DataIsToBeSaved", "NameOfFieldWhereColumn3DataIsToBeSaved")
select "DatePlayed", "Column2", "Column3"
from "Temp"

[Nocton]

Thank you both. I did not think of that. I was using 'Select *'.