There are two identical sites, one of them is a test one, for experiments. The client accidentally added new products to the database on the test site. I want to pull these records out of the database, I have never done this, I read a SQL course, the plan is: 1. Pull these records out of the table with products by a query and create a new table from them:

SELECT * INTO 'test_products' FROM `nkvzs_jshopping_products` WHERE `date_modify`> '2017-09-17 00:00:00.000';
  1. Export a new table from the database and import it into the base of the main site.

  2. Merge two tables together with a command

    SELECT * FROM frdsy_jshopping_products UNION SELECT * FROM test_products ;

But the new table is not created automatically, phpmyadmin swears that it does not exist. I don't want to create manually. Is this generally the right way of thinking? How to act?

    2 answers 2

    new table is not automatically created

    And should not. Create it explicitly with a query.

     CREATE TABLE test_products LIKE nkvzs_jshopping_products;

    And then insert the necessary records into it.

    Merge two tables together with a command

    The SELECT query does not merge anything. Well, that is, you will see the records in one list, of course, but from this they will not merge into one table, the output of records is not even storage at all.

    Is this generally the right way of thinking?

    Let's just say - one of the many relatively correct, well, or similar to the right. Without knowing the database schema, to say, it is completely correct, partially, or to the correct and not close, alas, it is impossible.

    • Thanks, helpful. Is the output of the UNION command just a list? And how to save this result in a table? Through UPDATE or INSERT it is possible? - varella
    • Well, either INSERT INTO ... (SELECT ... UNION SELECT ...) , or UPDATE ... , (SELECT ... UNION SELECT ...) AS ... SET ... - Akina
    • Thank you very much, I understood. And how to avoid duplication errors on the primary key? UPDATE replaces the records, and INSERT swears at the duplicate id_product. - varella
    • how to avoid duplication error on the primary key? Once [phpmyadmin], then probably [mysql]. Then INSERT .. ON DUPLICATE KEY UPDATE or REPLACE. And duplicates in the source tables will be eliminated, because UNION DISTINCT. - Akina
    • Thank you very much! - varella

    If the format of the databases of both sites is the same , and the products do not have external links for any fields , then you can probably do it easier:

    1. It is important . Make backups of both sites and their bases
    2. Highlight products for transfer in the same PHPMyAdmin (manually or select via SELECT and select all selected)
    3. Click "Export" on the panel at the bottom of the table. The selected records are converted to SQL text of the request, in the end, after the shamanism with the settings, you can get just a set of INSERT for the same database
    4. Get the resulting SQL text and execute it on the combat database.
    5. When swearing at PRIMARY KEY from INSERT requests, remove the PRIMARY KEY values ​​from both parts of the INSERT - well, that is, provide the database with the PRIMARY KEY value
    6. In case of an error or a result that differs from the desired one, return to point 1, recovering the data from the backups made. Consider mistakes and repeat

    If files, images, any connections are tied to goods, and so on, then simply mixing or adding in pure SQL will not work. You need to personally approach the task, find out the logic of each field and correctly transfer them. Better a script connected to two databases at once. We take from the first, analyze, carefully insert into the second

    UPD: In case records have connections, you need to remove the entire record, along with all connections.

    This means that if, for example, record A has three more related records AB, AC, AD in other tables (images, documents, rights, viewing statistics, something else), then it is easier to make a script with approximately the following algorithm:

    1. Connect to test database
    2. Take out records of interest to us
    3. Remove all related records
    4. Disconnect from the test database, connect to the combat database
    5. Add all related materials to the appropriate tables (AB, AC, AD from the example above). Get the new value of the field on which the link is built
    6. In the master record A, change all the fields responsible for the connections to the new values ​​from the associated AB, AC, AD
    7. Add master record with updated link values

    These are complex operations, and in pure SQL it is quite difficult to do them. It is easier to write a script, which is given the identifier of the record in the test database, which must be transferred to the battle database. And transfer them with a script, neatly

    • Yes, there, of course, several tables are connected, the table of images is exactly there. But you can carry out this sequence of operations with all of them. Is it possible to export selected records to phpmyadmin? At me it turned out either the table entirely, or "since any". And I did not understand this: "in the end, after shamanism with the settings, you can get just a set of INSERT for the same database." Isn't there just a table being obtained, but it can only be imported into a new database? - varella
    • If your format is the same, you need the table itself without need. We need records of these tables to insert them into the combat database. And the problem with images, etc. is that communications in the test database will be guaranteed not to coincide with the combat database. For example, you have a record with image_id = 53, and in the combat database there is no such image at all, or (worse,) image_id = 53 is already taken by another image. - wirtwelt
    • In phpMyAdmin, I just look at the table in the Overview, for example, put 4 checkboxes to the left of the records I need, poke a link under the Export table and in the export settings I say Display as text, as a result I get the SQL text to insert these 4 lines into INSERT (<fields>) VALUES (<r1>), (<r2>), (<r3>), (<r4>) . If you remove the PRIMARY KEY field from all the lines and the <fields> list, then this query can be executed on the combat base, and add 4 lines of interest to the combat base - wirtwelt
    • Updated the answer, added recommendations about the transfer of records with links. General advice, requires you to think. Good luck! ) - wirtwelt
    • Thank you, you really helped me, it seems, it turned out! - varella