SET NOCOUNT ON

DECLARE @TheCountryCode varchar(3)

SELECT @TheCountryCode = CountryCode FROM Countries WITH (NOLOCK) 
WHERE CountryNumber = (SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'COUNTRYCODE')

IF @TheCountryCode = 'N' 
BEGIN
  IF NOT EXISTS (SELECT GroupName FROM CountryGroups WITH (NOLOCK) WHERE GroupName = 'Israel') 
    INSERT INTO CountryGroups (GroupName, SortNumber2, GroupCode) VALUES  ('Israel', 51, 'IL')

  DECLARE @CountryGroupNumber INT

  SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupCode = 'IL'

  IF NOT EXISTS (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryName = 'Israel') 
  BEGIN
	  INSERT INTO Countries (CountryName, CountryCode, MarkAsDeleted, CountryGroupNumber2, CurrencyCode, CurrencyName, SortNumber, ISO_3166_A2, ISO_3166_A3) VALUES 
      ('Israel', 'IL', 0, @CountryGroupNumber, 'ILS', 'New Israeli Sheqel', 999, 'IL', 'ISR')
  END
  ELSE
  BEGIN 
    UPDATE Countries 
    SET CountryGroupNumber2 = @CountryGroupNumber, MarkAsDeleted = 0, CurrencyCode = 'ILS', CurrencyName = 'New Israeli Sheqel', CountryCode = 'IL', ISO_3166_A2 = 'IL', ISO_3166_A3 ='ISR'
    WHERE CountryName = 'Israel'   
  END
END