Magento – COPY PRODUCT DATA FROM ONE MAGENTO STORE VIEW TO ANOTHER

I found recently that adding a new store view to Magento was a pretty straightforward task, however I needed all products on the new store view to be the same as another. All new store-view catalog data inherits from the default store scope instead of a sibling store-view’s data. Copying this content from one store view to another through the Magento admin area is a very cumbersome task, especially if you have a large catalog of products with store-view specific data. I also didn’t think it was worth running code through a controller, so I decided to dust off my old SQL boots and take the fight to the database. This could easily be modified to take a product_id parameter as well, so get your teeth in.

Ultimately this wasn’t overly difficult, and it’s probably a useful exercise for someone trying to learn EAV. Since EAV bloats out tables quite significantly, there’s a number of tables to consider. I think I’ve covered them all; at least I have for my purposes. Let me know if there’s something you encounter that isn’t covered here and I’ll be sure to add it.

Also, as usual, always try this on a development server with a separate database.

All you have to do is change the ID’s defined at the start.

/*********************************************************
 *              Magento Product Data Copier              *
 *              mikebywaters.wordpress.com               *
 *********************************************************/

# DEFINE
SET @to_store := 6;      # the store_id of the recipient store
SET @from_store := 2;    # the store_id of the donor store

/*********************************************************
 * catalog_product_entity                                *
 *********************************************************/

# PROCESS datetime VALUES
DELETE FROM catalog_product_entity_datetime
WHERE store_id = @to_store;
INSERT INTO catalog_product_entity_datetime (
    store_id,
    entity_type_id,
    attribute_id,
    entity_id,
    value
) SELECT
    @to_store,
    entity_type_id,
    attribute_id,
    entity_id,
    value
FROM catalog_product_entity_datetime
WHERE store_id = @from_store;


# PROCESS decimal VALUES
DELETE FROM catalog_product_entity_decimal
WHERE store_id = @to_store;
INSERT INTO catalog_product_entity_decimal (
    store_id,
    entity_type_id,
    attribute_id,
    entity_id,
    value
) SELECT
    @to_store,
    entity_type_id,
    attribute_id,
    entity_id,
    value
FROM catalog_product_entity_decimal
WHERE store_id = @from_store;


# PROCESS gallery VALUES
DELETE FROM catalog_product_entity_gallery
WHERE store_id = @to_store;
INSERT INTO catalog_product_entity_gallery (
    store_id,
    entity_type_id,
    attribute_id,
    entity_id,
    position,
    value
) SELECT
    @to_store,
    entity_type_id,
    attribute_id,
    entity_id,
    position,
    value
FROM catalog_product_entity_gallery
WHERE store_id = @from_store;

# PROCESS int VALUES
DELETE FROM catalog_product_entity_int
WHERE store_id = @to_store;
INSERT INTO catalog_product_entity_int (
    store_id,
    entity_type_id,
    attribute_id,
    entity_id,
    value
) SELECT
    @to_store,
    entity_type_id,
    attribute_id,
    entity_id,
    value
FROM catalog_product_entity_int
WHERE store_id = @from_store;

# PROCESS text VALUES
DELETE FROM catalog_product_entity_text
WHERE store_id = @to_store;
INSERT INTO catalog_product_entity_text (
    store_id,
    entity_type_id,
    attribute_id,
    entity_id,
    value
) SELECT
    @to_store,
    entity_type_id,
    attribute_id,
    entity_id,
    value
FROM catalog_product_entity_text
WHERE store_id = @from_store;

# PROCESS varchar VALUES
DELETE FROM catalog_product_entity_varchar
WHERE store_id = @to_store;
INSERT INTO catalog_product_entity_varchar (
    store_id,
    entity_type_id,
    attribute_id,
    entity_id,
    value
) SELECT
    @to_store,
    entity_type_id,
    attribute_id,
    entity_id,
    value
FROM catalog_product_entity_varchar
WHERE store_id = @from_store;


/*********************************************************
 * catalog_product_entity_media_gallery                  *
 *********************************************************/

# PROCESS value VALUES
DELETE FROM catalog_product_entity_media_gallery_value
WHERE store_id = @to_store;
INSERT INTO catalog_product_entity_media_gallery_value (
    store_id,
    value_id,
    label,
    position,
    disabled
) SELECT
    @to_store,
    value_id,
    label,
    position,
    disabled
FROM catalog_product_entity_media_gallery_value
WHERE store_id = @from_store;

# IM UP TO HERE

/*********************************************************
 * catalog_product_option                                *
 *********************************************************/

# PROCESS price VALUES
DELETE FROM catalog_product_option_price
WHERE store_id = @to_store;
INSERT INTO catalog_product_option_price (
    store_id,
    option_id,
    price,
    price_type
) SELECT
    @to_store,
    option_id,
    price,
    price_type
FROM catalog_product_option_price
WHERE store_id = @from_store;

# PROCESS title VALUES
DELETE FROM catalog_product_option_title
WHERE store_id = @to_store;
INSERT INTO catalog_product_option_title (
    store_id,
    option_id,
    title
) SELECT
    @to_store,
    option_id,
    title
FROM catalog_product_option_title
WHERE store_id = @from_store;

# PROCESS type_price VALUES
DELETE FROM catalog_product_option_type_price
WHERE store_id = @to_store;
INSERT INTO catalog_product_option_type_price (
    store_id,
    option_type_id,
    price,
    price_type
) SELECT
    @to_store,
    option_type_id,
    price,
    price_type
FROM catalog_product_option_type_price
WHERE store_id = @from_store;

# PROCESS type_title VALUES
DELETE FROM catalog_product_option_type_title
WHERE store_id = @to_store;
INSERT INTO catalog_product_option_type_title (
    store_id,
    option_type_id,
    title
) SELECT
    @to_store,
    option_type_id,
    title
FROM catalog_product_option_type_title
WHERE store_id = @from_store;

/*********************************************************
 * catalog_product_super_attribute                       *
 *********************************************************/

# PROCESS label VALUES
DELETE FROM catalog_product_super_attribute_label
WHERE store_id = @to_store;
INSERT INTO catalog_product_super_attribute_label (
    store_id,
    product_super_attribute_id,
    use_default,
    value
) SELECT
    @to_store,
    product_super_attribute_id,
    use_default,
    value
FROM catalog_product_super_attribute_label
WHERE store_id = @from_store;

/*********************************************************
 * catalog_product_bundle_option                         *
 *********************************************************/

# PROCESS value VALUES
DELETE FROM catalog_product_bundle_option_value
WHERE store_id = @to_store;
INSERT INTO catalog_product_bundle_option_value (
    store_id,
    option_id,
    title
) SELECT
    @to_store,
    option_id,
    title
FROM catalog_product_bundle_option_value
WHERE store_id = @from_store;

Revisions

No comments yet.

Leave a Reply