How to insert values through inner join
First add Category information
Get ID number against that Category using function SCOPE_IDENTITY() and save value to a variable  
Then use product detials and that varible to insert values in Product table 


.

DECLARE @CategoryName VARCHAR(200),
        @Category_id INT


SET @CategoryName='CategoryName1';        

INSERT INTO Category(CategoryName)
SELECT @CategoryName;


SELECT @Category_id= SCOPE_IDENTITY();    --- 


INSERT INTO Product(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
SELECT 'ProductName', @Category_id , 'ProductCompany', 'ProductPrice', 'ProductQty';