Append Query Like Microsoft Access

[kenlyle2]

Hey, I am moving my e-commerce system from one shopping cart, which I habitually do by using Access and ODBC and setting up Append queries.

I am trying to move away from Access, but can't see any obvious way to do an Append query in SQLyog.

I looked at the Import External Data Powertool, but it requires me to inject a query, which I would rather not write by hand.

Anyway, if you are familiar, Access has an easy interface where you can pick which table to append to, and if the field names match, double clicking the field name in the source table adds it to the query, and makes the association to the field of the same name in the target.

Is there something similar in Yog?

Thanks for reading.

[peterlaursen]

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

[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.

[peterlaursen]

Yes .. currently there is no other way. You may use the Query Builder to build the SELECT.  Once you know it returns what it should I would find it trivial to copy it and prefix with INSERT INTO …

Of course such could be implemented in the client (SQLyog).  But I doubt we ever will.  Access is an office/desktop database system primarily – MySQL is a database server.  it is not the first time that we reply users that it is Access that is *different*.  Access does not define standards for us. It is rather the most non-standard program you can imagine in the database world (but convenient in use in lots of contexts). Could I ask you if you find such option in any official client for Microsoft SQL Server?

Anyway I have added a reference to the discussion here: http://code.google.com/p/sqlyog/issues/detail?id=1539 where we had a similar request.  One problem is (as described) issues with  (more or less 'silent') truncation of INSERTed data that the MySQL server may do if column types and column lengths don't match the result set of the SELECT.  SQLyog cannot check that – it will have to try .. and  maybe or maybe not the server will return an error or a warning for the n'th row (after all rows before n succeeded). I would not dare the operation on an important database unless using transactions that can be ROLLED BACK.

[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.

[peterlaursen]

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

[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.

[peterlaursen]

We promised that we would reply back here. We have checked this in Access.

Now – in Access the 'suer layer' and the 'storage layer' are able to communicate more directly (through the binary code of the program) than a client can communicate with the MySQL server. A client for the MySQL server has only two options to communicate with the server

1) client API calls

2) sending SQL

The client API has no such option ('append' is totally unknown to MySQL), so it will have to be done in SQL. So (a little polemical) I could ask you what the SQL should be to achieve this? I will have to be INSERT INTO .. (SELECT ..). But the problem is that there is no way in MySQL to ensure that it works if the result set does not match the table where you want to 'append'. There is not even an option to check this in advance (MySQL has no option to parse an INSERT statement except for executing it). So all sorts of errors could occur and user will only know when it is too late (is. when the target table has been corrupted). With transactional storage engines it could be wrapped in a transaction and rolled back if an error or warning occurs, but with MyISAM it is impossible.

Anyway the request has been added to the thread in our issue-tracker hwere we already had a similar request for implementing INSERT INTO .. (SELECT ..) in the GUI. But this does not guarantee taht we will ever do it. And for foreseeable future we won't.

[ken]

Thanks again.

Your ideas about transactions, rollback, etc. seem really good.

You could call this feature “Best Effort Append” 🙂

Since you can clone tables, etc., maybe there should be a Wizard, and the steps could be

• select target table for append
• to clone the target structure, optionally with our without data
• to run the insert
• report back some metrics
• open a new browser on the test table
• offer to run the BEA on the production table

I appreciate your considering this.

[jaxrobinson]

I will try and use this append software as soon as possible..

[Author]

Posts