Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Export result of multiple queries

Eduard_F's profile image Eduard_F posted 4 weeks ago in Feature discussion Permalink

I need to export a customer from a magento 2 shop with all related data. The data is spread over multiple tables. I am using 10 queries in total, for example:

-- customer_entity

    SELECT * FROM customer_entity
    WHERE entity_id = XXXX;

-- customer_entity_int

    SELECT * FROM customer_entity_int
    WHERE entity_id = XXXX;

-- customer_entity_varchar

    SELECT * FROM customer_entity_varchar
    WHERE entity_id = XXXX;

...

This shows this result:

image description

Now I need to go through each table, mark all rows, export them to my clipboard and paste them into an editor, one by one, until I have all results, like this:

INSERT INTO `customer_entity` (`entity_id`, `website_id`, `email`, `group_id`, `increment_id`, `store_id`, `created_at`, `updated_at`, `is_active`, `disable_auto_group_change`, `created_in`, `prefix`, `firstname`, `middlename`, `lastname`, `suffix`, `dob`, `password_hash`, `rp_token`, `rp_token_created_at`, `default_billing`, `default_shipping`, `taxvat`, `confirmation`, `gender`, `failures_num`, `first_failure`, `lock_expires`, `activasion_status`, `mp_smtp_email_marketing_synced`) VALUES (5217, 1, 'info@xxxx.com', 7, NULL, 1, '2021-08-26 17:06:20', '2022-06-10 13:35:42', 1, 0, 'Default Store View', 'Herr', 'Max Mustermann', NULL, 'Max', NULL, NULL, 'xxxxxxxxxxx:yyyyyyyyyyy:zzzzzzzzzzz', NULL, NULL, 5813, 5813, NULL, NULL, 0, 0, NULL, NULL, 222, 0);

INSERT INTO `customer_entity_int` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (660180, 141, 5217, 0);
INSERT INTO `customer_entity_int` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (660181, 142, 5217, 0);
INSERT INTO `customer_entity_int` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (660179, 143, 5217, 1630153393);
INSERT INTO `customer_entity_int` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (660177, 144, 5217, 1);
INSERT INTO `customer_entity_int` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (660178, 214, 5217, 0);
INSERT INTO `customer_entity_int` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (660080, 226, 5217, 1629997580);
INSERT INTO `customer_entity_int` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (661749, 519, 5217, 0);


INSERT INTO `customer_entity_varchar` (`value_id`, `attribute_id`, `entity_id`, `value`) VALUES (74284, 224, 5217, '"info@xxxx.com"');

This is a tedious task, especially if you have many queries.


Is there a way to automate this and export all results at once?

ansgar's profile image ansgar posted 4 weeks ago Permalink

If you just wanted the whole tables to be exported as INSERTs, you should use Tools > "Export database as SQL"

Description

But I see you have a filter to all queries, which is not doable in the export dialog. If you want to export some rows only, you need to use the grid exporter, by hand.

ansgar's profile image ansgar posted 4 weeks ago Permalink

As an alternative, you could use a mysqldump command line:

mysqldump.exe -h127.0.0.1 -uroot -p --no-create-info --result-file=name="customer-4574.sql" --where="entity_id=4574" magento customer_entity customer_entity_int customer_entity_varchar
Eduard_F's profile image Eduard_F posted 4 weeks ago Permalink

The mysqldump command is no alternative since I have many more queries which are more complex like this one:

-- sales_order_tax_item

    SELECT * FROM sales_order_tax_item
    WHERE tax_id IN (
        SELECT tax_id FROM sales_order_tax
        WHERE order_id IN (
            SELECT entity_id FROM sales_order
            WHERE customer_id = XXXX
        )
    );

It would be nice if heidi would implement it as a new feature.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.