Ask Your Question

Appending data to Base Table fails

asked 2015-10-20 14:02:09 +0100

BobGlen gravatar image

updated 2015-10-20 18:34:51 +0100

Alex Kemp gravatar image

I feel really stupid, I have followed the posts on OOO and here on Libre Office but no matter what, I cannot get an append of additional columns to succeed.

Existing table (SalesHistorySep15) has a primary key (not auto key) ItemNo is primary key and has several other columns SalesJan13 to SalesSep15 I now want to add column SalesOct15. I open a calc file and connect to database (F4) and drag and drop table into calc sheet. Create a new column SalesOct15 and Populate with data.

Have tried several Methods. Here below is one. I can document the other methods and the different errors, if its a real problem, at this point I suspect I have missed something basic and the fault lies with my me. 1. Create new column in Base called SalesOct15. 2. Copy ItemNo (Primary Key) and SalesOct15 in Calc. Paste into table in Base. 3. Choose Append Data and Use 1st Row as column names. 4. Move SalesOct15 up to second position to match position on Source Receive following Error. Violation of Unique Contraint SYS_PK_165: duplicate value(s) for Column(s) "Item No" in statement {INSERT INTO "SalesHistorySep15" ("ItemNo",SalesOCT15") VALUES (??)} Continue anyway ...Yes No.

Have a great day


LIBREOffice Version: (x64) Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe Java 8 Update 60 (64 bit) Windows 10 (64 bit)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-10-21 12:11:56 +0100

peterwt gravatar image

updated 2015-10-21 15:15:29 +0100

When you attempt to copy from Calc to the Table in Base it will try to create new records in the Base table with the Columns ItemNo and SalesOct15. Since the Table already has the ItemNo and it is the Primary Key (which does not allow duplicates) you get the error "duplicate values" where SYS_PK_ is the Primary Key Index for ItemNo.

Why not just create the new Column SalesOct15 in the Base Table and then open the Table and enter the data for SalesOct15 directly into the Table. I can see no reason to copy the Table to Calc, create the new Column, and copy back to Base.

edit flag offensive delete link more


@BobGlen: The following goes beyond your question. You need to examine your database structure. 1) You should not need a field such as "SalesOct15". 2) I have found it easier and Base complains less if you have an auto-increment primary key. 3) Use Base as a front-end, use another (open-source) database as the back-end.

Steve R. gravatar imageSteve R. ( 2015-10-21 15:56:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-10-20 14:02:09 +0100

Seen: 561 times

Last updated: Oct 21 '15