MySQL - Trouble With Bulk Insert Statements

[suicide1405241470 0]

I'm trying to insert about 500 rows into mysql, but I keep getting errors. If I copy and paste too many (about 50) insert statements at a time I get errors sometimes. I sometimes even get errors but then the row is skipped so I don't know there was an error (I'm using linux and SSH). What's the best way to get my insert statements to put the data in MySQL? Is there anyway that I can have it tell me if there where any errors all the statements are executed?Thanks for your help.

[iGuest 3]

bulk insertion into two tables

MySQL - Trouble With Bulk Insert Statements

 

Hi

Can anybody help me previously I am able to work with bulk insertion

But I don't know how to use it for insertion into two tables

Here is my criteria

I am having two tables

1) labdata

2) labdatainfo

In labdatainfo there is one auto generated column this column is the forgein keyfor the labdata table when a record is inserted it should first insert in labdatainfo and then it should be inserted in labdata

Can anybody help me

 

-question by sumanth

[darasen 0]

There is really not enough information to answer you question completely. Most likely you will need more than one INSERT statement. The RDBMS may have a non ANSI SQL method for inserting into multiple tables with one statement though. Your best bet is to write a procedure for what ever insertion method you are using.

[Arbitrary 0]

HiCan anybody help me previously I am able to work with bulk insertion
But I don't know how to use it for insertion into two tables
Here is my criteria
I am having two tables
1) labdata
2) labdatainfo
In labdatainfo there is one auto generated column this column is the forgein keyfor the labdata table when a record is inserted it should first insert in labdatainfo and then it should be inserted in labdata
Can anybody help me

Just write two insertion statements, one that inserts the data into labdatainfo and one into labdata. So say your insertion statement for labdatainfo is something like the following:

mysql_query("INSERT INTO labdatainfo (title, data) VALUES ('theTitle', 'theData')");

After you call this, php has a nice function called mysql_insert_id() that will retrieve the primary key of the most recent insert. So you would then call mysql_insert_id, grab the unique id and then insert that id into labdata as the foreign key. I.e:

$id = mysql_insert_id();mysql_query("INSERT INTO labdata (foreignkey) VALUES ('$id')");

The key to your question is probably just that php function. :-) It's quite the obscure function too, for something so useful.

As for your question suicide, I'm not sure what you're asking. Are you unable to automate the data that you have to insert instead of writing 500 different insert statements?
Edited July 20, 2008 by Arbitrary (see edit history)

[iGuest 3]

Generateing Insert StatementsMySQL - Trouble With Bulk Insert Statements

Most data bases can generate DDL for any object but not a lot of them allow generation of INSERT statements for the table data.The workaround is to make use of ETL Tools for transferring data across servers. However, there exists a need to generate INSERT statements from the tables for porting data.Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT..VALUES statements come handy.There is a number of scripts available to perform this data transformation task. The problem with those scripts that all of them database specific and they do not work with textilesAdvanced ETL processor can generate Insert scripts from any data source including text file 

-reply by Mike