How to insert values through inner join Could anybody tell me how to do insert statement with inner join.

i am trying to insert 
ProductName,ProductCompany,ProductPrice,ProductQty in Product table and CategoryName in category Table. And i also want to fetch Category_id to the Category_id_FK.

I am using SQL Server 2008
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';