SET QUOTED_IDENTIFIER OFF
GO
CREATE  
VIEW [dbo].[AL_COLLECTED_COMPANY_VIEW]
AS
  SELECT DISTINCT Top 100 PERCENT
      company_uuid,
      company_name,
      authoritative,
      version_number,
      source_type_id
      FROM ca_company
   UNION ALL
   Select Top 100 PERCENT
    convert(binary(16), newid()) as company_uuid,
    sub.company_name as company_name,
    3 as authoritative,
      0 as version_number,
      1 as source_type_id
    FROM
      (SELECT DISTINCT Top 100 PERCENT
        dh.vendor_name as company_name
       FROM ca_discovered_hardware dh INNER JOIN ca_agent ap
            ON ap.object_uuid = dh.dis_hw_uuid) as sub
    WHERE company_name is not null
        AND NOT EXISTS (select 1 from al_norm_manufacturer nm where nm.collected_value = company_name)
GO