[kenlyle2]

Thanks very much!

Best,

Ken

[kenlyle2]

'peterlaursen' wrote:

After executing a query, rigth-click in the result area, select “Export All Rows ..” and click “SQL” -option. This will save a .sql.file that can next be executed.

Very good, Thanks, and I note the warnings.

'peterlaursen' wrote:

Also you may simply execute “CREATE TABLE `new_table` as SELECT .. ;”

Great! So I just get the SELECT working, then put CREATE TABLE `new_table` as in front? Nice.

So that solves my issue, Thanks.

Could you consider making this even easier for noobs, by adding CREATE TABLE as a right click option?

[kenlyle2]

'peterlaursen' wrote:

Please give us a few day to try this APPEND option in Access ourselves.

Wow, Cool.

You just right click in the query builder area and select query type append query, and it asks you which table you want to append to. Then, each time you double click on a source field, it adds to the query, and if there's an exact match in the fields of the target, the two are associated in the query. OR, you can work from the target side, and drop down in the lower row in the query grid, the Append To fields, and build up your query by selecting the target fields, and then deciding which source field matches or putting in a 1 or some other constant. Overall, it's super easy to use, no writing anything.

I look forward to seeing what you come up with. Let me know if I can help.

[kenlyle2]

Thanks, Peter. In this application, I am moving data into a new store that's offline, so I can just sit with Access on one machine and PHMMyAdmin on the other, and empty the table in PHPMA if anything goes wrong, then refresh Access to confirm that it's empty, modify and re-run the insert. I find it pretty slick. It lets me isolate the Microsoft virus on my HP Mini, and use my Ubuntu laptop for most of my work. So, I do have a form of rollback.

I do find Access very convenient, and, yes, the syntax is non-standard, but it's just a tool, not a religion or a spouse, so I don't mind.

I think that building the field list for the Insert Into part of the query wouldn't be hard, just harder and more error-prone than Access. I also use Access to bundle the related queries in the mdb which keeps them together and is almost self-documenting when I have to go back, based on the query names and the timestamp. It's just something I have absorbed into my workflow.

It's not important that every tool can suit every purpose, so I think we're all cool, and I'm glad to have both in my toolkit. BTW, it's only because SQLYog does so much that we ask for even more 🙂

Thanks.

[kenlyle2]

'peterlaursen' wrote:

MySQL does not have Append queries.  it is not a client problem.  It is a server limitation

A simple INSERT INTO .. (SELECT ..) is how to handle this in MySQL

Refer: http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

Thanks, but it's not simple. There are at least 6 tables of data to be moved, some of the inserts are based on joins, some of the inserts are constants, like set `published` to 1 in the new table. The dropdown field names in Access make it easy to make sure that everything matches correctly, and to run the query as a select first to see what will be inserted.

To do the same in SQLYog, it sounds like I have to build the select statement, then create the insert into field list manually?

Thanks.

[Author]

Posts