Importing data from existing DB table into new

[qec]

So as I explained here I’ve been unable to change the primary key to autogenerate in an existing database table, and it seems creating a new table and importing all my other data is the easiest way to deal with it. How exactly would I do this?

I’m running LibreOffice 7.0.0.3 on Windows 10.

[frofa]

Some questions first:

1. Do you have more than a single ‘target’ table in your database? (NOTE: I am calling the ‘target’ table the one you want to modify its PK to autovalue)

2. If ‘yes’ to 1 above, are there foreign keys (FKs) referencing the PK values in your ‘target’ table (that makes life more complicated because the PK values existing your ‘target’ table can’t be changed if they reference existing FKs in other tables.)

3. It might help if you could post a SANITIZED (with some dummy data) TEST BASE file of database so we can see the SCHEMA (design) of your tables, or a SCREENSHOT of your table-edit window for each of your tables.

What we’re aiming to do is create a NEW empty “target” table with an autovalue PK and import your original table into it without changing any of your existing PK values, and then delete the original “target” table. (Anyone else, please feel free to help with this problem.)

[qec]

Sorry for the slow response on this–been swamped with all kinds of work and so haven’t had the chance to look at this anymore. The only table I want to change is the primary key. I’m happy to just create a new table and just copy the data over if that would be easier, but I’m not sure how to do that either–when I tried to just copy/paste the row it only copied the info from the first row. I’ve edited the question to include screenshots of a sample of the sort of data and the table edit window.

[Ratslinger]

Hello,

Created & tested this process some time ago. Should work for you also.

For this it is best to save a copy of the Base file before proceeding - backup in case you have a problem.

 1)  Copy the original table and paste with a new name.
 2)  Delete the original table name
 3)  Open the copied original table in Edit mode
 4)  Select ‘Create Table in Design View’
 5)  In copied original table, in leftmost column (left of first field name) select all fields.  Do this by left mouse click on first field line, then hold down the shift key and the left mouse click on last field line.  Then either “Ctrl+c” or right mouse click & select “Copy”.
 6)  In new table definition, right mouse click on first field line (again left of field name area) and select “Paste”
 7)  Now modify the auto increment field to your choice of Yes or NO.
 8)  Save the new table (use the original table name – before it was copied).
 9)  Close copied original & new table definitions.
10)  Right mouse click copied original table name & select “Copy”
11)  Right mouse click newly defined table name & select “Paste”
       a)  Dialog appears
       b)  Insure ‘Table name:’ is correct
       c)  Insure ‘Append data’ is selected
       d)  Click on ‘Create’ button

Verify newly table is correct. Then if so, delete other table data was copied from.

You should also reset the starting value of the auto increment field:

Get the largest current value of the key. Can get by running query:

Select MAX("ID") from "YOUR_TABLE_NAME"

Then, using that value, from menu on main screen of .odb, Tools->SQL… enter:

alter table "YOUR_TABLE_NAME"  alter "ID" restart with NNNN;

where NNNN is the value of the highest primary key obtained.

Test inserting a new record.