Bulk Insert into view and find missing values
For the purpose of this answer, I am assuming your INSERT statement looks something like this:

INSERT INTO
  dbo.vSubCategories (CategoryName, SubCategory)
SELECT
  CategoryName,
  SubCategory
FROM
  ...  /* reading rows either from the CSV directly or
          from a staging table into which the CSV has been imported */
;


So, the first thing your trigger needs to do is to insert the (new) categories into tbCategories:

INSERT INTO
  dbo.tbCategories (CategoryName)
SELECT
  CategoryName
FROM
  inserted
EXCEPT  -- do not insert already existing category names
SELECT
  CategoryName
FROM
  dbo.tbCategories
;


Now all the category names in the imported dataset can be mapped to their IDs using the tbCategories table. And that is what the trigger should do when inserting the subcategories:

INSERT INTO
  dbo.tbSubCategories (CategoryID, SubCategory)
SELECT
  c.CategoryID,
  n.SubCategory
FROM
  inserted AS n
  INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
EXCEPT  -- exclude existing sub-categories, just in case
SELECT
  CategoryID,
  SubCategory
FROM
  dbo.tbSubCategories
;


For reference, here is a complete trigger script:

CREATE TRIGGER dbo.vSubCategories_InsteadOfInsert
ON dbo.vSubCategories
INSTEAD OF INSERT
AS
BEGIN
  -- insert categories
  INSERT INTO
    dbo.tbCategories (CategoryName)
  SELECT
    CategoryName
  FROM
    inserted
  EXCEPT  -- do not insert already existing category names
  SELECT
    CategoryName
  FROM
    dbo.tbCategories
  ;
  -- insert sub-categories
  INSERT INTO
    dbo.tbSubCategories (CategoryID, SubCategory)
   SELECT
    c.CategoryID,
    n.SubCategory
  FROM
    inserted AS n
    INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
  EXCEPT  -- exclude existing sub-categories, just in case
  SELECT
    CategoryID,
    SubCategory
  FROM
    dbo.tbSubCategories
  ;
END;