Custom Columns in Calibre have a sequential number assigned when table custom_columns is updated for a new CC. Your "Target" metadata.db has none of your CCs, so there is no Target CC for you to copy from Main to Target (yet). After you have totally updated all of your books and their Standard Metadata in the Target, you could copy a book from Main and have Calibre automatically create whichever CCs you wish. Then, they would exist. You would then have to add new SQL that is specific to the CC number in your Main and the CC number in your Target. They will NOT be the same, since the Q&S Target metadata.db comes with a dozen "standard" Q&S "work" CCs.
Depending on the datatype of your Target CC, you will have either 1 or 2 CC tables.
Those with only 1 CC table: have book id in the table. One line of SQL suffices for this type.
Code:
INSERT OR REPLACE INTO custom_column_[N] (id,book,value) VALUES (null,?,?)
do a commit here...unless your SQL tool does it automatically for you after each SQL end of statement, ";".
Those with 2 CC tables: have a "values" table that does NOT have book id in it, and then a table that "links" the book to the "values" table. 2 lines of SQL are required for this type of CC. The first line must always be for the "values" table, and the second line for the "link" table. The value must already exist before it can be used in the second line, so the first line's database updates must be committed after the first line and before the second line.
Code:
INSERT OR REPLACE INTO custom_column_N (id,value) VALUES (null,?)
do a commit here...unless your SQL tool does it automatically for you after each SQL end of statement, ";".
INSERT OR REPLACE INTO books_custom_column_N_link (id, book, value) VALUES (null,?,?)
do a commit here...unless your SQL tool does it automatically for you after each SQL end of statement, ";".
Note: The value of the link table is NOT the value of the values table; it is the "id" of the values table having the real value that each book uses.
You see why this is for Technical Users, since it is up to you to figure out how to replace the "?"s with the correct SQL logic.
Hint 1: you will find the generic answer if you look at the SQL for table "books_authors_link", and use that as a pattern for your CCs having 2 tables.
Hint 2: for CCs with only a single table, use the SQL for table "identifiers" as the example.
There are web sites that can teach you SQLite SQL, such as
http://www.sqlitetutorial.net/.
DaltonST