Copying Data Between Tables

[ukwiz]

I have a table in one DB from which I want to extract the data in 4 columns, then import that data into a table in another DB. I thought that exporting the data was the best option because you can specify columns to be extracted.

I was offered XML, HTML and CSV options. I chose XML. Fine, data exported.

Went to import – no XML import. If you can export XML, why can you not import it.

OK then, export to CSV. Chose Excel friendly option – exported fine

Tried to import into table – selected table and columns, click import – got message “error while importing data”.

Looked for log – nada, nothing. Pressed help on dialog box – just showed how to import.

How do I either find out what the problem is, or how can I do what I originally wanted?

Regards

David

[peterlaursen]

1) SQLyog does not import XML yet, that is right

2) CSV: try to read: http://www.webyog.com/faq/28_73_en.html. Could be a problem with escaping! Or that the table that you import to does not have the right structure (sttructure must be defined in advance, at a CSV has no such inforamtion in it). It is not possible to anyone to help more here without a small set of data that generates this. + create statement for the table + a screendump of the settings.

3) Another way is to create a new table with a subset of data using this SQL

Code:

Create table my_table select my_column1,my_column2 ect  from… where .. limit etc.

It creates a new table named my_table (in the same database) with the (subset of) data that the select finds. That new my_table can then be exported/imported (to CSV or SQL), 'copied to other host', or sync'ed.

4) Copy complete table and thereafter drop irrelevant columns

I might have forgot something!

[Ritesh]

SQLyog uses MySQL's inbuilt LOAD LOCAL COMMAND which will be logged into the history tab.

Just execute that query in the query window. What do you get?

[Author]

Posts