[Solved] Import into Autonumber field

[dgsarnow]

I am working with Openoffice 3.0 in a Vista environment. I have been using OO for three years now.

I am trying to import some data from a calc spreadsheet into a base table. I am using the drag and drop method. I have a column in the spreadsheet that includes an ID number (essentially an autonumber field). I would like to make this column import as an integer with autonumber option set to Yes, but I cannot figure how.

When I import it and try to change it, I get the warning that it cannot be changed and the only option is to delete it and append it. I do not want to do this as I would lose reference between the ID number and the record.

Any thoughts?

Dave

[Villeroy]

Import with a new auto-ID and the old ID related to foreign keys in other tables. Then do something like:

Code: Select all

UPDATE "TableX" SET "Foreign_Key" = "MainTable"."Auto_ID" WHERE "Foreign_Key" = "MainTable"."Old_ID"

[dgsarnow]

Villeroy thanks for the help. I am note sure I quite got it though.

The table I am importing has the unique id field called "SchoolID". This is the one that is connected to other tables. When I import it I can add a primary called "tempID" (this is the one OO will set to autogenerate). This is what I did with what you gave me:

UPDATE "schools" SET "SchoolID" = "tempID" WHERE "SchoolID" = "MainTable"."Old_ID"

I am not sure what the old id is. I just have the one table so far so I am not sure what to do.

Cheers,

Dave

[Villeroy]

Your SchoolID is supposed to be the old one. Keep it with the new auto-ID and when you import one of the related tables, say "Pupils", you update the existing "Pupils"."SchoolID" with the new, auto-generated one.

Table "Pupils":
P_ID, School_ID
0 77
1 88

Table "Schools":
new AutoID, old School_ID
1 77
2 88

After the UPDATE query in Tools>SQL... "Pupils should look like:
Table "Pupils":
P_ID, School_ID
0 1
1 2
Then you create the new relation and when you are shure that Schools.SchoolID is no longer needed you can remove that column.

Edit: Found the place where I outlined a different procedure: http://www.oooforum.org/forum/viewtopic.phtml?t=61763 There I created a new table from the imported spreadsheet tables by means of an INSERT statement.