 -- =============================================
-- Script Template
-- =============================================

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
MERGE INTO [lookup_table] AS DEST
USING (
VALUES
(N'1',N'South African ID',N'id_no_type',1,1)
,(N'2',N'Passport',N'id_no_type',2,1)
,(N'3',N'Unknown',N'id_no_type',3,1)
,(N'M',N'Male',N'gender',1,1)
,(N'F',N'Female',N'gender',2,1)
,(N'1',N'HIV',N'comorbidity',1,1)
,(N'2',N'Ischaemic heart disease',N'comorbidity',2,1)
,(N'3',N'Hypertension',N'comorbidity',3,1)
,(N'4',N'COPD/Asthma',N'comorbidity',4,1)
,(N'5',N'Diabetes Mellitus',N'comorbidity',5,1)
,(N'6',N'Pulmonary Tuberculosis',N'comorbidity',6,1)
,(N'7',N'Other (please specify)',N'comorbidity',7,1)
,(N'patient',N'Patient',N'entitytype',1,1)
,(N'provider',N'Medical Services Provider',N'entitytype',2,1)
,(N'A',N'African',N'PDI',1,1)
,(N'C',N'Coloured',N'PDI',2,1)
,(N'I',N'Indian',N'PDI',3,1)
,(N'W',N'White',N'PDI',4,1)
,(N'N',N'Non-South African',N'PDI',5,1)
,(N'O',N'Other',N'PDI',5,1)
,(N'Z',N'Not Indicated',N'PDI',6,1)
,(N'Adv',N'Adv',N'title',1,1)
,(N'Dr',N'Dr',N'title',2,1)
,(N'Miss',N'Miss',N'title',3,1)
,(N'Mr',N'Mr',N'title',4,1)
,(N'Mrs',N'Mrs',N'title',5,1)
,(N'Ms',N'Ms',N'title',6,1)
,(N'Prof',N'Prof',N'title',7,1)
,(N'Rev',N'Rev',N'title',8,1)
,(N'ZA',N'South Africa',N'country',1,1)
,(N'NA',N'Namibia',N'country',2,1)
,(N'AF',N'Afghanistan',N'country',3,1)
,(N'AL',N'Albania',N'country',4,1)
,(N'DZ',N'Algeria',N'country',5,1)
,(N'AS',N'American Samoa',N'country',6,1)
,(N'AD',N'Andorra',N'country',7,1)
,(N'AO',N'Angola',N'country',8,1)
,(N'AI',N'Anguilla',N'country',9,1)
,(N'AQ',N'Antarctica',N'country',10,1)
,(N'AG',N'Antigua and Barbuda',N'country',11,1)
,(N'AR',N'Argentina',N'country',12,1)
,(N'AM',N'Armenia',N'country',13,1)
,(N'AW',N'Aruba',N'country',14,1)
,(N'AU',N'Australia',N'country',15,1)
,(N'AT',N'Austria',N'country',16,1)
,(N'AZ',N'Azerbaijan',N'country',17,1)
,(N'BS',N'Bahamas',N'country',18,1)
,(N'BH',N'Bahrain',N'country',19,1)
,(N'BD',N'Bangladesh',N'country',20,1)
,(N'BB',N'Barbados',N'country',21,1)
,(N'BY',N'Belarus',N'country',22,1)
,(N'BE',N'Belgium',N'country',23,1)
,(N'BZ',N'Belize',N'country',24,1)
,(N'BJ',N'Benin',N'country',25,1)
,(N'BM',N'Bermuda',N'country',26,1)
,(N'BT',N'Bhutan',N'country',27,1)
,(N'BO',N'Bolivia',N'country',28,1)
,(N'BA',N'Bosnia and Herzegovina',N'country',29,1)
,(N'BW',N'Botswana',N'country',30,1)
,(N'BR',N'Brazil',N'country',31,1)
,(N'IO',N'British Indian Ocean Territory',N'country',32,1)
,(N'VG',N'British Virgin Islands',N'country',33,1)
,(N'BN',N'Brunei',N'country',34,1)
,(N'BG',N'Bulgaria',N'country',35,1)
,(N'BF',N'Burkina Faso',N'country',36,1)
,(N'BI',N'Burundi',N'country',38,1)
,(N'KH',N'Cambodia',N'country',39,1)
,(N'CM',N'Cameroon',N'country',40,1)
,(N'CA',N'Canada',N'country',41,1)
,(N'CV',N'Cape Verde',N'country',42,1)
,(N'KY',N'Cayman Islands',N'country',43,1)
,(N'CF',N'Central African Republic',N'country',44,1)
,(N'TD',N'Chad',N'country',45,1)
,(N'CL',N'Chile',N'country',46,1)
,(N'CN',N'China',N'country',47,1)
,(N'CX',N'Christmas Island',N'country',48,1)
,(N'CC',N'Cocos (Keeling) Islands',N'country',49,1)
,(N'CO',N'Colombia',N'country',50,1)
,(N'KM',N'Comoros',N'country',51,1)
,(N'CK',N'Cook Islands',N'country',52,1)
,(N'CR',N'Costa Rica',N'country',53,1)
,(N'HR',N'Croatia',N'country',54,1)
,(N'CU',N'Cuba',N'country',55,1)
,(N'CY',N'Cyprus',N'country',56,1)
,(N'CZ',N'Czech Republic',N'country',57,1)
,(N'CD',N'Democratic Republic of the Congo',N'country',58,1)
,(N'DK',N'Denmark',N'country',59,1)
,(N'DJ',N'Djibouti',N'country',60,1)
,(N'DM',N'Dominica',N'country',61,1)
,(N'DO',N'Dominican Republic',N'country',62,1)
,(N'EC',N'Ecuador',N'country',63,1)
,(N'EG',N'Egypt',N'country',64,1)
,(N'SV',N'El Salvador',N'country',65,1)
,(N'GQ',N'Equatorial Guinea',N'country',66,1)
,(N'ER',N'Eritrea',N'country',67,1)
,(N'EE',N'Estonia',N'country',68,1)
,(N'ET',N'Ethiopia',N'country',69,1)
,(N'FK',N'Falkland Islands',N'country',70,1)
,(N'FO',N'Faroe Islands',N'country',71,1)
,(N'FJ',N'Fiji',N'country',72,1)
,(N'FI',N'Finland',N'country',73,1)
,(N'FR',N'France',N'country',74,1)
,(N'PF',N'French Polynesia',N'country',75,1)
,(N'GA',N'Gabon',N'country',76,1)
,(N'GM',N'Gambia',N'country',77,1)
,(N'GE',N'Georgia',N'country',78,1)
,(N'DE',N'Germany',N'country',79,1)
,(N'GH',N'Ghana',N'country',80,1)
,(N'GI',N'Gibraltar',N'country',81,1)
,(N'GR',N'Greece',N'country',82,1)
,(N'GL',N'Greenland',N'country',83,1)
,(N'GD',N'Grenada',N'country',84,1)
,(N'GU',N'Guam',N'country',85,1)
,(N'GT',N'Guatemala',N'country',86,1)
,(N'GN',N'Guinea',N'country',87,1)
,(N'GW',N'Guinea-Bissau',N'country',88,1)
,(N'GY',N'Guyana',N'country',89,1)
,(N'HT',N'Haiti',N'country',90,1)
,(N'VA',N'Holy See (Vatican City)',N'country',91,1)
,(N'HN',N'Honduras',N'country',92,1)
,(N'HK',N'Hong Kong(China)',N'country',93,1)
,(N'HU',N'Hungary',N'country',94,1)
,(N'IS',N'Iceland',N'country',95,1)
,(N'IN',N'India',N'country',96,1)
,(N'ID',N'Indonesia',N'country',97,1)
,(N'IR',N'Iran',N'country',98,1)
,(N'IQ',N'Iraq',N'country',99,1)
,(N'IE',N'Ireland',N'country',100,1)
,(N'IM',N'Isle of Man',N'country',101,1)
,(N'IL',N'Israel',N'country',102,1)
,(N'IT',N'Italy',N'country',103,1)
,(N'CI',N'Ivory Coast',N'country',104,1)
,(N'JM',N'Jamaica',N'country',105,1)
,(N'JP',N'Japan',N'country',106,1)
,(N'JE',N'Jersey',N'country',107,1)
,(N'JO',N'Jordan',N'country',108,1)
,(N'KZ',N'Kazakhstan',N'country',109,1)
,(N'KE',N'Kenya',N'country',110,1)
,(N'KI',N'Kiribati',N'country',111,1)
,(N'KW',N'Kuwait',N'country',112,1)
,(N'KG',N'Kyrgyzstan',N'country',113,1)
,(N'LA',N'Laos',N'country',114,1)
,(N'LV',N'Latvia',N'country',115,1)
,(N'LB',N'Lebanon',N'country',116,1)
,(N'LS',N'Lesotho',N'country',117,1)
,(N'LR',N'Liberia',N'country',118,1)
,(N'LY',N'Libya',N'country',119,1)
,(N'LI',N'Liechtenstein',N'country',120,1)
,(N'LT',N'Lithuania',N'country',121,1)
,(N'LU',N'Luxembourg',N'country',122,1)
,(N'MO',N'Macau',N'country',123,1)
,(N'MK',N'Macedonia',N'country',124,1)
,(N'MG',N'Madagascar',N'country',125,1)
,(N'MW',N'Malawi',N'country',126,1)
,(N'MY',N'Malaysia',N'country',127,1)
,(N'MV',N'Maldives',N'country',128,1)
,(N'ML',N'Mali',N'country',129,1)
,(N'MT',N'Malta',N'country',130,1)
,(N'MH',N'Marshall Islands',N'country',131,1)
,(N'MR',N'Mauritania',N'country',132,1)
,(N'MU',N'Mauritius',N'country',133,1)
,(N'YT',N'Mayotte',N'country',134,1)
,(N'MX',N'Mexico',N'country',135,1)
,(N'FM',N'Micronesia',N'country',136,1)
,(N'MD',N'Moldova',N'country',137,1)
,(N'MC',N'Monaco',N'country',138,1)
,(N'MN',N'Mongolia',N'country',139,1)
,(N'ME',N'Montenegro',N'country',140,1)
,(N'MS',N'Montserrat',N'country',141,1)
,(N'MA',N'Morocco',N'country',142,1)
,(N'MZ',N'Mozambique',N'country',143,1)
,(N'NR',N'Nauru',N'country',144,1)
,(N'NP',N'Nepal',N'country',145,1)
,(N'NL',N'Netherlands',N'country',146,1)
,(N'AN',N'Netherlands Antilles',N'country',147,1)
,(N'NC',N'New Caledonia',N'country',148,1)
,(N'NZ',N'New Zealand',N'country',149,1)
,(N'NI',N'Nicaragua',N'country',150,1)
,(N'NE',N'Niger',N'country',151,1)
,(N'NG',N'Nigeria',N'country',152,1)
,(N'NU',N'Niue',N'country',153,1)
,(N'NF',N'Norfolk Island',N'country',154,1)
,(N'KP',N'North Korea',N'country',155,1)
,(N'MP',N'Northern Mariana Islands',N'country',156,1)
,(N'NO',N'Norway',N'country',157,1)
,(N'OM',N'Oman',N'country',158,1)
,(N'PK',N'Pakistan',N'country',159,1)
,(N'PW',N'Palau',N'country',160,1)
,(N'PA',N'Panama',N'country',161,1)
,(N'PG',N'Papua New Guinea',N'country',162,1)
,(N'PY',N'Paraguay',N'country',163,1)
,(N'PE',N'Peru',N'country',164,1)
,(N'PH',N'Philippines',N'country',165,1)
,(N'PN',N'Pitcairn Islands',N'country',166,1)
,(N'PL',N'Poland',N'country',167,1)
,(N'PT',N'Portugal',N'country',168,1)
,(N'PR',N'Puerto Rico',N'country',169,1)
,(N'QA',N'Qatar',N'country',170,1)
,(N'CG',N'Republic of the Congo',N'country',171,1)
,(N'RO',N'Romania',N'country',172,1)
,(N'RU',N'Russia',N'country',173,1)
,(N'RW',N'Rwanda',N'country',174,1)
,(N'BL',N'Saint Barthelemy',N'country',175,1)
,(N'SH',N'Saint Helena',N'country',176,1)
,(N'KN',N'Saint Kitts and Nevis',N'country',177,1)
,(N'LC',N'Saint Lucia',N'country',178,1)
,(N'MF',N'Saint Martin',N'country',179,1)
,(N'PM',N'Saint Pierre and Miquelon',N'country',180,1)
,(N'VC',N'Saint Vincent and the Grenadines',N'country',181,1)
,(N'WS',N'Samoa',N'country',182,1)
,(N'SM',N'San Marino',N'country',183,1)
,(N'ST',N'Sao Tome and Principe',N'country',184,1)
,(N'SA',N'Saudi Arabia',N'country',185,1)
,(N'SN',N'Senegal',N'country',186,1)
,(N'RS',N'Serbia',N'country',187,1)
,(N'SC',N'Seychelles',N'country',188,1)
,(N'SL',N'Sierra Leone',N'country',189,1)
,(N'SG',N'Singapore',N'country',190,1)
,(N'SK',N'Slovakia',N'country',191,1)
,(N'SI',N'Slovenia',N'country',192,1)
,(N'SB',N'Solomon Islands',N'country',193,1)
,(N'SO',N'Somalia',N'country',194,1)
,(N'KR',N'South Korea',N'country',195,1)
,(N'ES',N'Spain',N'country',196,1)
,(N'LK',N'Sri Lanka',N'country',197,1)
,(N'SD',N'Sudan',N'country',198,1)
,(N'SR',N'Suriname',N'country',199,1)
,(N'SJ',N'Svalbard',N'country',200,1)
,(N'SZ',N'Swaziland',N'country',201,1)
,(N'SE',N'Sweden',N'country',202,1)
,(N'CH',N'Switzerland',N'country',203,1)
,(N'SY',N'Syria',N'country',204,1)
,(N'TW',N'Taiwan',N'country',205,1)
,(N'TJ',N'Tajikistan',N'country',206,1)
,(N'TZ',N'Tanzania',N'country',207,1)
,(N'TH',N'Thailand',N'country',208,1)
,(N'TL',N'Timor-Leste',N'country',209,1)
,(N'TG',N'Togo',N'country',210,1)
,(N'TK',N'Tokelau',N'country',211,1)
,(N'TO',N'Tonga',N'country',212,1)
,(N'TT',N'Trinidad and Tobago',N'country',213,1)
,(N'TN',N'Tunisia',N'country',214,1)
,(N'TR',N'Turkey',N'country',215,1)
,(N'TM',N'Turkmenistan',N'country',216,1)
,(N'TC',N'Turks and Caicos Islands',N'country',217,1)
,(N'TV',N'Tuvalu',N'country',218,1)
,(N'UG',N'Uganda',N'country',219,1)
,(N'UA',N'Ukraine',N'country',220,1)
,(N'AE',N'United Arab Emirates',N'country',221,1)
,(N'GB',N'United Kingdom',N'country',222,1)
,(N'US',N'United States',N'country',223,1)
,(N'UY',N'Uruguay',N'country',224,1)
,(N'VI',N'US Virgin Islands',N'country',225,1)
,(N'UZ',N'Uzbekistan',N'country',226,1)
,(N'VU',N'Vanuatu',N'country',227,1)
,(N'VE',N'Venezuela',N'country',228,1)
,(N'VN',N'Vietnam',N'country',229,1)
,(N'WF',N'Wallis and Futuna',N'country',230,1)
,(N'EH',N'Western Sahara',N'country',231,1)
,(N'YE',N'Yemen',N'country',232,1)
,(N'ZM',N'Zambia',N'country',233,1)
,(N'ZW',N'Zimbabwe',N'country',234,1)
,(N'GZ',N'Gaza Strip',N'country',235,1)
,(N'KV',N'Kosovo',N'country',236,1)
,(N'WE',N'West Bank',N'country',237,1)
,(N'AX',N'Aland Islands',N'country',238,1)
,(N'BV',N'Bouvet Island',N'country',239,1)
,(N'GP',N'Guadeloupe',N'country',242,1)
,(N'GG',N'Guernsey',N'country',243,1)
,(N'HM',N'Heard Island and Mcdonald Islands',N'country',244,1)
,(N'MQ',N'Martinique',N'country',245,1)
,(N'MM',N'Myanmar',N'country',246,1)
,(N'PS',N'Palestinian Territory; Occupied',N'country',247,1)
,(N'RE',N'Réunion',N'country',248,1)
,(N'GS',N'South Georgia and the South Sandwich Islands',N'country',249,1)
,(N'SS',N'South Sudan',N'country',250,1)
,(N'UM',N'United States Minor Outlying Islands',N'country',251,1)
,(N'1',N'Postal',N'patient_address_type',1,1)
,(N'2',N'Work',N'patient_address_type',2,1)
,(N'3',N'Home',N'patient_address_type',3,1)
,(N'4',N'Physical',N'patient_address_type',4,1)
,(N'5',N'Billing',N'patient_address_type',5,1)
,(N'6',N'Other',N'patient_address_type',6,1)
,(N'1',N'Postal',N'provider_address_type',1,1)
,(N'2',N'Work',N'provider_address_type',2,1)
,(N'4',N'Billing',N'provider_address_type',4,1)
,(N'5',N'Other',N'provider_address_type',5,1)
,(N'Home',N'Home',N'phone_number_type',1,1)
,(N'Work',N'Work',N'phone_number_type',2,1)
,(N'Mobile',N'Mobile',N'phone_number_type',3,1)
,(N'1',N'Sole Proprietor',N'company_types',1,1)
,(N'2',N'Partnership',N'company_types',2,1)
,(N'3',N'Closed Corporation',N'company_types',3,1)
,(N'4',N'Proprietary Limited',N'company_types',4,1)
,(N'Provider',N'Provider Details',N'provider_registration_process',1,1)
,(N'ProviderAddress',N'Provider Address Details',N'provider_registration_process',2,1)
,(N'Practice',N'Practice Details',N'provider_registration_process',3,1)
,(N'PracticeAddress',N'Practice Address',N'provider_registration_process',4,1)
,(N'PracticeUnit',N'Practice Unit Details',N'provider_registration_process',5,1)
,(N'PracticeBanking',N'Practice Banking Details',N'provider_registration_process',6,1)
,(N'PracticeCompany',N'Practice Company Details',N'provider_registration_process',7,1)
,(N'SubmitRegistrationRequest',N'Send Registration Request',N'provider_registration_process',8,1)
,(N'ProviderRegistrationComplete',N'Registration Complete',N'provider_registration_process',9,1)
,(N'1',N'None',N'provider_speciality_type',1,1)
,(N'2',N'Anaesthetics',N'provider_speciality_type',2,1)
,(N'3',N'Child health',N'provider_speciality_type',3,1)
,(N'4',N'Diabetes and Endocrine',N'provider_speciality_type',4,1)
,(N'5',N'Dermatology',N'provider_speciality_type',5,1)
,(N'6',N'HIV',N'provider_speciality_type',6,1)
,(N'7',N'Mental health',N'provider_speciality_type',7,1)
,(N'8',N'Respiratory and Allergies',N'provider_speciality_type',8,1)
,(N'9',N'Women’s health',N'provider_speciality_type',9,1)
,(N'1',N'Level 1',N'bbbee_status',1,1)
,(N'2',N'Level 2',N'bbbee_status',2,1)
,(N'3',N'Level 3',N'bbbee_status',3,1)
,(N'4',N'Level 4',N'bbbee_status',4,1)
,(N'5',N'Level 5',N'bbbee_status',5,1)
,(N'6',N'Level 6',N'bbbee_status',6,1)
,(N'7',N'Level 7',N'bbbee_status',7,1)
,(N'8',N'Level 8',N'bbbee_status',8,1)
,(N'9',N'Non-compliant',N'bbbee_status',9,1)
,(N'10',N'Not Sure',N'bbbee_status',10,1)
,(N'textbox',N'Textbox',N'form_element_type',1,1)
,(N'dropdown',N'Dropdown',N'form_element_type',2,1)
,(N'bool',N'Boolean',N'field_data_type',1,1)
,(N'float',N'Float',N'field_data_type',2,1)
,(N'int',N'Integer',N'field_data_type',3,1)
,(N'string',N'String',N'field_data_type',4,1)
,(N'1',N'Auth Created',N'auth_process_status',1,1)
,(N'2',N'Auth Submitted',N'auth_process_status',2,1)
,(N'3',N'Auth Re-submitted',N'auth_process_status',3,1)
,(N'4',N'Auth Pended',N'auth_process_status',4,1)
,(N'5',N'Auth Closed',N'auth_process_status',5,1)
,(N'6',N'Auth Cloned',N'auth_process_status',6,1)
,(N'1',N'Chronic Medicine',N'auth_type',1,1)
,(N'2',N'Facility',N'auth_type',2,1)
,(N'3',N'Product',N'auth_type',3,1)
,(N'4',N'Service',N'auth_type',4,1)
,(N'5',N'Full',N'auth_type',5,1)
,(N'1',N'Medicine',N'product_type',1,1)
,(N'2',N'Non-Medicine',N'product_type',2,1)
,(N'30',N'per day',N'product_frequency',1,1)
,(N'4',N'per week',N'product_frequency',2,1)
,(N'2',N'every two weeks',N'product_frequency',3,1)
,(N'1',N'per month',N'product_frequency',4,1)
,(N'1',N'script',N'dispensing_type',1,1)
,(N'2',N'dispense',N'dispensing_type',2,1)
,(N'1',N'Awaiting Approval',N'auth_scheme_status',1,1)
,(N'2',N'Approved',N'auth_scheme_status',2,1)
,(N'3',N'Rejected',N'auth_scheme_status',3,1)


--,(N'3',N'Closed Corporation',N'business_type',1,1)
--,(N'1',N'Sole Proprietor',N'business_type',2,1)
--,(N'4',N'Proprietary Limited',N'business_type',3,1)
--,(N'2',N'Partnership',N'business_type',4,1)
) AS SRC ([lookup_dropdown_value], [lookup_dropdown_description], [lookup_type], [lookup_display_order], [active])
ON (DEST.[lookup_dropdown_value] = SRC.[lookup_dropdown_value] AND DEST.[lookup_type] = SRC.[lookup_type])
WHEN MATCHED THEN
  UPDATE SET
  [lookup_dropdown_description] = SRC.[lookup_dropdown_description]
  ,[lookup_type] = SRC.[lookup_type]
  ,[lookup_display_order] = SRC.[lookup_display_order]
  ,[active] = SRC.[active] 
WHEN NOT MATCHED THEN
  INSERT ([lookup_dropdown_value],[lookup_dropdown_description], [lookup_type], [lookup_display_order],[active])
  VALUES (SRC.[lookup_dropdown_value], SRC.[lookup_dropdown_description], SRC.[lookup_type], SRC.[lookup_display_order], SRC.[active]);

  
COMMIT;
RAISERROR (N'lookup_table: Insert [lookup_table] Reference Data: .....Done!', 10, 1) WITH NOWAIT;
GO

 -- INSERT INTO [dbo].[lookup_table] ( [lookup_dropdown_value], [lookup_dropdown_description], [lookup_type], [lookup_display_order], [active]) 
 -- VALUES (N'Provider', N'Provider Details', N'provider_registration_process', 1, 1)
