views:

44

answers:

1

I have an excel sheet with around 2.000 rows that i want to insert into my database.

The problem is that the table i want to insert the 2.000 rows into has a field that references to a foreign key in another table. Unfortunately a lot of queries fail, since the given foreign key does NOT exist.

I know that I can ignore foreign key checks, but this is not what i want. I don't want to ignore foreign key checks, I just want bad queries not to be executed.

Example:

INSERT INTO test (id, value) VALUES (10, 20);
INSERT INTO test (id, value) VALUES (20, 20);

The first query fails, since TEST.id references to foobar.id and there is no foobar.id=10. However, the second query would work, since foobar.id=20 exists, but the second query won't be executed, because the first one already failed. Is there any way I don't get an error on the first quiery and my other queries will still be executed?

I could write a php script, but I'd prefer a MySQL solution here.

+2  A: 

You could change to insert the result of a select, so something like:

INSERT INTO test (id, value) 
SELECT foobar.id, 20
FROM foobar WHERE id = 10;
Rowland Shaw
Have to add limit 1, otherwise each insert could be multiple rows.
MJB
@MJB I'm assuming that id is the primary key on `foobar`, per the OP, so the `LIMIT` *shouldn't* be needed.
Rowland Shaw
If I do it like that, it's only possible to insert data into "test", which is already in "foobar", right? But I have new data to add, which comes from the excel sheet. Can i somehow adjust this, so i can add new data to it?
@Rowland - I mis-read. I think you are right, and I retract my comment.
MJB
@snootles Have you the information to create the record in `foobar`? If so you could instead pre-create the values, perhaps using a query along the lines of `INSERT INTO foobar (id,...) SELECT 10, ... FROM test LEFT JOIN foobar ON test.id = foobar.id WHERE foobar.id IS NULL`
Rowland Shaw