MySQL load data and skip key

Posted on 2011-10-17
Last Modified: 2012-05-12
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?
Question by:mph23
    LVL 24

    Expert Comment

    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.

    Author Comment

    Any reason for the temp table?
    LVL 24

    Accepted Solution

    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.
    LVL 24

    Expert Comment

    OR, when you export the data from the source database, do not include the primary key column.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now