﻿/* Делает из базы @DB1 базу @DB2, фильтруя данные по стране @COU_ID: оставляет только изделия, применимые к стране */

SET @DB1 = 'tecdoc_2012_2q_full'; -- Исходная база данных
SET @DB2 = 'tecdoc_2012_2q_russia'; -- Конечная база данных
SET @COU_ID_CDS = 204; -- Код страны для таблицы COUNTRY_DESIGNATIONS
SET @COU_ID = 204; -- Код страны для остальных таблиц: 186 - Россия; 223 - Украина; 33 - Беларусь; 204 - экс-СССР; 52 - Германия; 248 - Европа
SET @COLUMN_FILTER = '_CTM'; -- Удалять все колонки, содержащие в названии эту строку (NULL без кавычек = не удалять)
SET @ROW_FILTER = TRUE; -- TRUE - Фильтровать строки таблиц по стране; FALSE - Просто скопировать все строки

/* Фильтрование данных в таблицах по стране @COU_ID */

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ACL_CTM, @COU_ID+2, 1)="1"
', 'ACCESSORY_LISTS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ACC_CTM, @COU_ID+2, 1)="1"
', 'ACL_CRITERIA'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ACS_CTM, @COU_ID+2, 1)="1"
	', 'ART_COUNTRY_SPECIFICS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ARL_CTM, @COU_ID+2, 1)="1"
', 'ART_LOOKUP'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ACR_CTM, @COU_ID+2, 1)="1"
', 'ARTICLE_CRITERIA'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(AIN_CTM, @COU_ID+2, 1)="1"
', 'ARTICLE_INFO'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ALC_CTM, @COU_ID+2, 1)="1"
', 'ARTICLE_LIST_CRITERIA'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ALI_CTM, @COU_ID+2, 1)="1"
', 'ARTICLE_LISTS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ART_CTM, @COU_ID+2, 1)="1"
', 'ARTICLES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(AXL_LA_CTM, @COU_ID+2, 1)="1"
', 'AXLES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(CPL_CTM, @COU_ID+2, 1)="1"
', 'CONST_PATTERN_LOOKUP'
);

SET @ROW_FILTER_OLD = @ROW_FILTER;
SET @ROW_FILTER = TRUE;
SET @COU_ID_OLD = @COU_ID;
SET @COU_ID = @COU_ID_CDS;
CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(CDS_CTM, @COU_ID+2, 1)="1" 
', 'COUNTRY_DESIGNATIONS'
);
SET @ROW_FILTER = @ROW_FILTER_OLD;
SET @COU_ID = @COU_ID_OLD;

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(CAB_CTM, @COU_ID+2, 1)="1"
', 'CV_CABS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(MRK_CTM, @COU_ID+2, 1)="1" AND SUBSTRING(MRK_LA_CTM, @COU_ID+2, 1)="1"
', 'CV_MARKS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(CST_CTM, @COU_ID+2, 1)="1"
', 'CV_SECONDARY_TYPES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		ENC_COU_ID=@COU_ID OR ENC_COU_ID=247
', 'ENG_COUNTRY_SPECIFICS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ENL_CTM, @COU_ID+2, 1)="1"
', 'ENG_LOOKUP'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(ENG_CTM, @COU_ID+2, 1)="1" AND SUBSTRING(ENG_LA_CTM, @COU_ID+2, 1)="1"
', 'ENGINES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAC_CTM, @COU_ID+2, 1)="1"
', 'LA_CRITERIA'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LIN_CTM, @COU_ID+2, 1)="1"
', 'LA_INFO'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LA_CTM, @COU_ID+2, 1)="1"
', 'LINK_ART'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LCT_CTM, @COU_ID+2, 1)="1"
', 'LINK_CAB_TYP'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LGC_CTM, @COU_ID+2, 1)="1"
', 'LINK_GA_CRI'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LGA_CTM, @COU_ID+2, 1)="1"
', 'LINK_GRA_ART'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LGL_CTM, @COU_ID+2, 1)="1"
', 'LINK_GRA_LA'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAA_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_AXL'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAAN_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_AXL_NEW'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAE_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_ENG'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAEN_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_ENG_NEW'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAM_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_MRK'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAMN_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_MRK_NEW'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LAT_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_TYP'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LATN_CTM, @COU_ID+2, 1)="1"
', 'LINK_LA_TYP_NEW'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LTE_CTM, @COU_ID+2, 1)="1"
', 'LINK_TYP_ENG'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(LMK_CTM, @COU_ID+2, 1)="1"
', 'LINK_TYP_MRK'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		(SUBSTRING(MFA_PC_CTM, @COU_ID+2, 1)="1" AND MFA_PC_MFC=1) OR
		(SUBSTRING(MFA_CV_CTM, @COU_ID+2, 1)="1" AND MFA_CV_MFC=1)
', 'MANUFACTURERS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(MTL_CTM, @COU_ID+2, 1)="1"
', 'MOD_TYP_LOOKUP'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		(SUBSTRING(MOD_PC_CTM, @COU_ID+2, 1)="1" AND MOD_PC="1") OR
		(SUBSTRING(MOD_CV_CTM, @COU_ID+2, 1)="1" AND MOD_CV="1")
', 'MODELS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(PRI_CTM, @COU_ID+2, 1)="1"
', 'PRICES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(SUA_CTM, @COU_ID+2, 1)="1"
', 'SUPERSEDED_ARTICLES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(SLO_CTM, @COU_ID+2, 1)="1"
', 'SUPPLIER_LOGOS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		TYC_COU_ID="@COU_ID" OR TYC_COU_ID=247
', 'TYP_COUNTRY_SPECIFICS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(TSU_CTM, @COU_ID+2, 1)="1"
', 'TYP_SUSPENSIONS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(TVO_CTM, @COU_ID+2, 1)="1"
', 'TYP_VOLTAGES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(TWB_CTM, @COU_ID+2, 1)="1"
', 'TYP_WHEEL_BASES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(TYN_CTM, @COU_ID+2, 1)="1"
', 'TYPE_NUMBERS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(TYP_CTM, @COU_ID+2, 1)="1" AND SUBSTRING(TYP_LA_CTM, @COU_ID+2, 1)="1"
', 'TYPES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SUBSTRING(UTD_CTM, @COU_ID+2, 1)="1"
', 'UTILITY_DIRECT'
);

/* Копирование оставшихся таблиц без изменений */

CALL COPY_TABLE('ALI_COORDINATES');

CALL COPY_TABLE('ARTICLES_NEW');

CALL COPY_TABLE('AXL_BRAKE_SIZES');

CALL COPY_TABLE('BRANDS');

CALL COPY_TABLE('COUNTRIES');

CALL COPY_TABLE('CRITERIA');

CALL COPY_TABLE('DES_TEXTS');

CALL COPY_TABLE('DESIGNATIONS');

CALL COPY_TABLE('DOC_TYPES');

CALL COPY_TABLE('ERR_TRACK_KEY_VALUES');

CALL COPY_TABLE('FILTERS');

CALL COPY_TABLE('GENERIC_ARTICLES');

CALL COPY_TABLE('GRAPHICS');

CALL COPY_TABLE('KEY_VALUES');

CALL COPY_TABLE('LANGUAGES');

CALL COPY_TABLE('LINK_ART_GA');

CALL COPY_TABLE('LINK_GA_STR');

CALL COPY_TABLE('LINK_SHO_STR');

CALL COPY_TABLE('LINK_SHO_STR_TYPE');

CALL COPY_TABLE('NUMBERPLATES_NL');

CALL COPY_TABLE('PARAMETERS');

CALL COPY_TABLE('SEARCH_TREE');

CALL COPY_TABLE('SHORTCUTS');

CALL COPY_TABLE('STR_FAMILY_TREE');

CALL COPY_TABLE('STR_LOOKUP');

CALL COPY_TABLE('SUPPLIER_ADDRESSES');

CALL COPY_TABLE('SUPPLIERS');

CALL COPY_TABLE('TEXT_MODULE_TEXTS');

CALL COPY_TABLE('TEXT_MODULES');
