SELECT
        DISTINCT
        C.[Firm Name],
        C.[Address Line 1] + ' ' + C.[Address Line 2],
        C.[City],
        C.[State],
        C.[Zip Code],
        C.[Bus# Phone Number],
        C.[Bus# FaxNumber],
        C.[Email],
        C.[Contact First Name],
        C.[Contact Last Name],
        @source,
        GETDATE(),
        @agencyID
        FROM dbo.CADBEImportData AS C LEFT JOIN AgencyCertifiedCompanies AS A
        ON C.[Firm Name] = A.[CompanyName] AND A.[Source] = @source
        WHERE A.[CompanyName] IS NULL AND C.[Firm Name] <> ''

    INSERT INTO AgencyCertifiedCompanies(
        [CompanyName],
        [Address],
        [City],
        [State],
        [Zip],
        [Phone],
        [Fax],
        [Email],
        [ContactFirstName],
        [ContactLastName],
        [Source],
        [AddedDate],
        [CertificationIssuerID]
    ) 
    SELECT
        DISTINCT
        C.[Firm Name],
        C.[Address Line 1] + ' ' + C.[Address Line 2],
        C.[City],
        C.[State],
        C.[Zip Code],
        C.[Bus# Phone Number],
        C.[Bus# FaxNumber],
        C.[Email],
        C.[Contact First Name],
        C.[Contact Last Name],
        @source,
        GETDATE(),
        @agencyID
        FROM dbo.CADBEImportData AS C LEFT JOIN AgencyCertifiedCompanies AS A
        ON C.[Firm Name] = A.[CompanyName] AND A.[Source] = @source
        WHERE A.[CompanyName] IS NULL AND C.[Firm Name] <> ''

CREATE TABLE tablename(id int)

insert into tablenameselect 1

select * from tablename