0

I have a db dump and its so huge(nearly 150gb). I want to insert this dump data to another schema which also has data. There are some identical rows and they gives Duplicate entry error when I try to import this dump. Is there a way to "insert if not exists while importing this dump"?

Filburt
  • 17,626
  • 12
  • 64
  • 115
hellzone
  • 5,393
  • 25
  • 82
  • 148
  • 1
    possible duplicate of [How to skip row when importing bad MySQL dump](http://stackoverflow.com/questions/7622253/how-to-skip-row-when-importing-bad-mysql-dump) – Ocaso Protal Jun 19 '15 at 09:25
  • 2
    Another possible duplicate: http://stackoverflow.com/questions/18485674/how-to-skip-duplicate-records-when-importing-in-phpmyadmin – Ocaso Protal Jun 19 '15 at 09:26

2 Answers2

1

You can use insert on duplicate key update:

insert into table1(id, col1, . . .)
     select id, col1, col2, . . 
     from table2
     on duplicate key update id = values(id);

For this to work, you need a unique index (or constraint) on the id column(s). Duplicates in this column are not allowed.

The on duplicate key part says to update the record when a duplicate is found. The id = values(id) is a no-op. The id is set to itself, so it does nothing and the effect of the statement is just to insert rows that are not in the original table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The user also may prefer renouncing to update as the original question possibly suggest by "if not exists". – OuzoPower Jun 23 '19 at 18:44
0

Well if I get you right - yes.

You can use EXCEPT in the INSERT commands.

See this example:

INSERT INTO yourTargetTable(name)
    SELECT name
    FROM yourSourceTable
    EXCEPT
    SELECT name
    FROM yourTargetTable
Ionic
  • 3,884
  • 1
  • 12
  • 33