• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

MySQL load data and skip key

I am trying to load data from one database to another. The databases are on different servers.

When I use the LOAD DATA command, I get errors when they are duplicate primary key values.

The syntax shows that you can specify the columns, thus I can skip the primary key column.

Is there a way to load the data without having to specify each column for each table?
0
mph23
Asked:
mph23
  • 3
1 Solution
 
johanntagleCommented:
Unfortunately, while you can choose what columns to load to, LOAD DATA assumes all columns from the source table are to be loaded.  There's no option to ignore a column from the source file.  Suggest you load it first to a temporary table then insert into the final table from the temporary table, specifying only the columns you want.  However, yes you still have to specify each column.
0
 
mph23Author Commented:
Any reason for the temp table?
0
 
johanntagleCommented:
Because while you can specify what columns on the tables to be loaded, LOAD DATA assumes all columns in the file to be imported are to be loaded.  So load everything to a temporary table first then copy to the final table using an INSERT.
0
 
johanntagleCommented:
OR, when you export the data from the source database, do not include the primary key column.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now