Davy Durham <[EMAIL PROTECTED]> wrote on 06/17/2005 12:41:24 PM: > Hi, > I was wondering if there's a way to make mysql not care about (or > ignore) columns in an insert statement that don't exist in the table. > For example, I have a dump from a table with 10 columns, but the new > table has one of the columns dropped.. well restoring from the dump > causes errors.. is there a way to ignore the unknown columns?
> This is something that's going to happen programatically and from time > to time. I know in the above example I can create the columns, then > drop them.. but I was looking for a simpler solution if there is one. > > Thanks, > Davy > How are you processing the incoming file (what command are you using to read your dump table)? If you are using LOAD DATA INFILE, there is a way to tell it to skip certain fields (and to create values for missing fields). If you are using a script (like those produced from mysqldump), you will need to load that data into a staging table then create your own INSERT statement using your freshly imported data as its source. http://dev.mysql.com/doc/mysql/en/load-data.html >>>>>>>>>>>>>>>>>>> By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: mysql> LOAD DATA INFILE 'persondata.txt' -> INTO TABLE persondata (col1,col2,...); You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match up input fields with table columns. If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented. If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in Section 13.2.5, ?CREATE TABLE Syntax?. An empty field value is interpreted differently than if the field value is missing: * For string types, the column is set to the empty string. * For numeric types, the column is set to 0. * For date and time types, the column is set to the appropriate ``zero'' value for the type. See Section 11.3, ?Date and Time Types?. <<<<<<<<<<<<<<<<< Shawn Green Database Administrator Unimin Corporation - Spruce Pine
