Copy product data from one store to another
All you have to do is change the ID’s defined at the start.
# 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;
The solution I used in the end was pretty simple. I gathered necessary attribute changes via array_diff_assoc and used the result in Mage::getSingleton('catalog/product_action')->updateAttributes()
EDIT:
The reason I opted out of direct DB query is quite simple, it's unsafe and I don't have much control over it. Using standard magento tools was also out of question since there were some customizations made to products that might result in incorrect data transfer. Checking all of the products to make sure it went well and no attributes are lost is out of the question.
So the solution I chose was shell script that would offer me some flexibility and control over entire process. What I did was really simple. I've gathered product collections for both stores in question. Compared them, logged the products that were not in one of the stores. I iterated through the rest of the products, getting the difference in attributes via array_diff_assoc and passing it to Mage::getSingleton('catalog/product_action')->updateAttributes() along with product_id and store_id