Create Slowly Changing Dimension table from Repeated Data
The problem here is the way you're loading data. With a Type 2 SCD (Effective Date) you want to add a new row only when there is a change to the data. The first four rows in your dataset do not change except for the load date.

You need to ETL your data from the source files into your database where you can more easily identify if records have been changed and only add new rows for the changed records. Begin Date would be the date that row is first loaded and End Date would be when one of the values changes marking the original row as no longer current. For the current row, End Date is NULL.

The query then becomes much simpler:

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,BeginDate
    ,CASE EndDate
        WHEN NULL THEN '12/31/9999'
        ELSE EndDate
     END AS EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory


The end result is your table would only have 3 rows rather than 10. If you're importing directly using TSQL scripts, look at using MERGE to UPDATE\INSERT as appropriate. If you're using an ETL tool like SSIS, they have native functions for handling SCDs. There are lots of ways to solve this, but the key is importing your data in a better way.