Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
digitalformula
Starting Member
2 Posts |
 Posted - 2010-09-04 : 05:53:12
|
| Hi all,Before registering I didn't realise that this forum was for SQL Server only but I'm hoping that the answer to this question will be similar for my database (MySQL) as it would be for SQL Server. :-)Anyway, I've got a simple table with 15 columns. They're a mix of data types i.e. varchar, int etc. This table holds comments from Wordpress. I've got another table with 15 columns (coincidental that the column count is the same). It's also a mix of varchar, int etc. The first table holds comments from Wordpress and the second holds comments from ExpressionEngine. The content is similar but obviously not identical. For example, the auto increment column for the Wordpress data is called 'id' while the equivalent auto increment column for ExpressionEngine is called 'comment_id' (both are int).If both tables are in the same database how would I write a query to get the data from the first table and put it into the second table if I know which column in the source table 'matches' a column in the destination table? For example ...id -> comment_id (PK in both)comment_post_id -> entry_iduser_id -> author_idThe datatypes, luckily, are the same for the columns in the source and destination tables with the exception of the comment date - in the source it's 'datetime' and the destination it's int(10) since EE uses a Unix timestamp for the date whereas WP uses an 'actual' date/time value.Can anyone help?Thanks! :-) |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-04 : 06:06:41
|
There is a forum at MySQL - in case you get better service there  http://forums.mysql.com/(If you post there/elsewhere please post a link to that question here - so people don't spend time giving you an answer you already got over there) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-04 : 06:10:35
|
| INSERT INTO TargetTable (TargetCol1, TargetCol2, ...)SELECT SourceCol1, SourceCol2, ...FROM SourceTablethat will insert all rows. You may want to add a column to the TargetTable to indicate the Source of the comment? Assuming the TargetTable is currently the wordpress data preset the value for the new column on all rows to "Wordpress" then the the import would be:INSERT INTO TargetWordPressTable (NewColumnName, TargetCol1, TargetCol2, ...)SELECT 'ExpressionEngine', SourceCol1, SourceCol2, ...FROM SourceExpressionEngineTableYou may also want to restrict WHICH rows you add to the TargetTable (if so explain the criteria which will restrict them and someone here can help with a WHERE or JOIN clause |
|
|
|
digitalformula
Starting Member
2 Posts |
Posted - 2010-09-04 : 08:55:49
|
quote:
Originally posted by Kristen
INSERT INTO TargetTable (TargetCol1, TargetCol2, ...)SELECT SourceCol1, SourceCol2, ...FROM SourceTablethat will insert all rows. You may want to add a column to the TargetTable to indicate the Source of the comment? Assuming the TargetTable is currently the wordpress data preset the value for the new column on all rows to "Wordpress" then the the import would be:INSERT INTO TargetWordPressTable (NewColumnName, TargetCol1, TargetCol2, ...)SELECT 'ExpressionEngine', SourceCol1, SourceCol2, ...FROM SourceExpressionEngineTableYou may also want to restrict WHICH rows you add to the TargetTable (if so explain the criteria which will restrict them and someone here can help with a WHERE or JOIN clause
PERFECT! Thank you! :-DThe JOIN clause for translating the comment_post_id values to the new entry_id values would be an interesting one to see, that's for sure. E.g. the entries in the new table are similar but not identical to the entries in the old table. It would have to include a fairly detailed LIKE clause I think. Hmmm man that's hard to explain ...It would need to look in the entries table in the new database and look for close matches in the entries in the old database. It would then need to look for comments in the new database that have the entry_id of the matching entries found in the old database (whose post_comment_id would be different) and then update the entry_id in the new database. I've made a complete mess of explaining that, unfortunately ... lol. :-) The reason for all that, though, is because although the comment and entry content is 99% the same, the id of the entries is different in every case because of the change from WP to EE. |
|
|
|
|
|
|
|
|
Copy data from 1 table 2 another (diff. structure)