Is there a way to import an sql dump into a table that has a different schema?

[jbubrisk]

I have an sql data dump, that I want to import into a table. The table is exactly the same, except that I added a timestamp column. I want to be able to capture when I import these records (really just the day). I'm using MySQL and the data dump is in the format of...

INSERT INTO `table` VALUES (value, value, value, value, value, value);

But obviously, this doesn't work since MySQL is expecting the last column, since there is no "columns" clause. Any ideas, other than adding either the column names or last value column to the data file?

Thanks in advance!
jbubrisk

 

[Crusty]

Read the man file for sed assuming this is a linux server. sed is designed to do exactly this, take an input stream and perform a text manipulation over and over again until it has no more input.

Your command would like something like

cat mysqldump.sql | sed $options | mysql -uroot $dbname

The other thing to do is to just delete the column from the DB until after you've loaded your data, then just add it back and set the default value.

 

[jbubrisk]

Well the real thing I'm trying to do is to keep track of each time I import the given dataset. So in the end, I would be able to see on which day a given record was imported. Maybe I could use a temp table or something? Import the data into the table, then after the import, copy the rows over to another table, inserting the timestamp...

Well its either that or sed. Any thoughts?

 

[Snapster]

Originally posted by: jbubrisk
I have an sql data dump, that I want to import into a table. The table is exactly the same, except that I added a timestamp column. I want to be able to capture when I import these records (really just the day). I'm using MySQL and the data dump is in the format of...

INSERT INTO `table` VALUES (value, value, value, value, value, value);

But obviously, this doesn't work since MySQL is expecting the last column, since there is no "columns" clause. Any ideas, other than adding either the column names or last value column to the data file?

Thanks in advance!
jbubrisk

Would this work?

INSERT INTO `table` VALUES (value, value, value, value, value, value, now());

 

[awal]

Originally posted by: jbubrisk
Well the real thing I'm trying to do is to keep track of each time I import the given dataset. So in the end, I would be able to see on which day a given record was imported. Maybe I could use a temp table or something? Import the data into the table, then after the import, copy the rows over to another table, inserting the timestamp...

Well its either that or sed. Any thoughts?

sed would definitely be the most elegant solution, but I dont see a problem with what you have suggested.

 

[jbubrisk]

Hi Snapster,
That is the problem. I already have an sql dump which has insert statements in it. Like awal said, I can "inject" those "now()" statements into the end of the insert as a solution.

 

[jbubrisk]

BTW, thanks to Crusty and awal!