Magento - Copy Store Views / Some tables are still missing

copy-store-view.sql

-- idea: rebuild this via php and use mysql.information_schema to automatically figure out tables with column store_id resp. website_id
-- maybe include it into n98-magerun

# DEFINE
SET @to_store := 8;
SET @to_website := 4;

SET @from_store := 2;
SET @from_website := 3;

/*********************************************************
 * MySQL config                                          *
 *********************************************************/
SET FOREIGN_KEY_CHECKS = 0;

/*********************************************************
 * EAV                                                   *
 *********************************************************/
DELETE FROM eav_attribute_label
WHERE store_id = @to_store;
INSERT INTO eav_attribute_label (`attribute_id`, `store_id`, `value`)
  SELECT
    `attribute_id`,
    @to_store,
    `value`
  FROM eav_attribute_label
  WHERE
    store_id = @from_store;

DELETE FROM eav_attribute_option_value
WHERE store_id = @to_store;
INSERT INTO eav_attribute_option_value (`option_id`, `store_id`, `value`)
  SELECT
    `option_id`,
    @to_store,
    `value`
  FROM eav_attribute_option_value
  WHERE
    store_id = @from_store;


/*********************************************************
 * catalog_search                                       *
 *********************************************************/
DELETE FROM catalogsearch_fulltext
WHERE store_id = @to_store;
INSERT INTO catalogsearch_fulltext (`product_id`, `store_id`, `data_index`)
  SELECT
    `product_id`,
    @to_store,
    `data_index`
  FROM catalogsearch_fulltext
  WHERE
    store_id = @from_store;

/*********************************************************
 * catalog_category_entity                                *
 *********************************************************/

# PROCESS datetime VALUES
DELETE FROM catalog_category_entity_datetime
WHERE store_id = @to_store;
INSERT INTO catalog_category_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_category_entity_datetime
  WHERE store_id = @from_store;

# PROCESS decimal VALUES
DELETE FROM catalog_category_entity_decimal
WHERE store_id = @to_store;
INSERT INTO catalog_category_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_category_entity_decimal
  WHERE store_id = @from_store;

# PROCESS int VALUES
DELETE FROM catalog_category_entity_int
WHERE store_id = @to_store;
INSERT INTO catalog_category_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_category_entity_int
  WHERE store_id = @from_store;

# PROCESS text VALUES
DELETE FROM catalog_category_entity_text
WHERE store_id = @to_store;
INSERT INTO catalog_category_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_category_entity_text
  WHERE store_id = @from_store;

# PROCESS varchar VALUES
DELETE FROM catalog_category_entity_varchar
WHERE store_id = @to_store;
INSERT INTO catalog_category_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_category_entity_varchar
  WHERE store_id = @from_store;


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

# PROCESS website ids VALUES
DELETE FROM catalog_product_website
WHERE website_id = @to_website;
INSERT INTO catalog_product_website (
  product_id,
  website_id
) SELECT
    product_id,
    @to_website
  FROM catalog_product_website
  WHERE website_id = @from_website;


# 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;


/*********************************************************
 * core url rewrite                                 *
 *********************************************************/
DELETE FROM core_url_rewrite
WHERE store_id = @to_store;
INSERT INTO core_url_rewrite (
  store_id,
  id_path,
  request_path,
  target_path,
  is_system,
  options,
  description,
  category_id,
  product_id
) SELECT
    @to_store,
    id_path,
    request_path,
    target_path,
    is_system,
    options,
    description,
    category_id,
    product_id
  FROM core_url_rewrite
  WHERE store_id = @from_store;


SET FOREIGN_KEY_CHECKS = 1;