Inserting distinct values and calculated field from one table to another

I’m inserting values from DailyFile into Department. The intended functionality is: only DepartmentName which doesn’t currently appear in dbo.Department is to be inserted, both DepartmentName and DepartmentNameAlternative are populated with the dbo.DailyFile.DepartmentName and Increment is incremented by one. The insert statement throws the following error:

Column 'dbo.DailyFile.DepartmentName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

DB script:

 CREATE TABLE dbo.Department
    (
        DepartmentNumber tinyint identity(1, 1) not null,
        DepartmentName varchar(50) NOT NULL,
        DepartmentNameAlternative varchar(50) NOT NULL,
        Increment tinyint null,
    )


    INSERT INTO dbo.Department (DepartmentName, DepartmentNameAlternative, Increment)
    VALUES
      ('HR', 'HR', 10)
    , ('IT', 'IT', 11)
    , ('Sales', 'Sales', 12);


    CREATE TABLE dbo.DailyFile
    (
        DepartmentName varchar(50) NOT NULL,
    )


    INSERT INTO dbo.DailyFile (DepartmentName)
    VALUES
      ('HR')
    , ('HR')
    , ('Sales')
    , ('IT')
    , ('Manufacturing')
    , ('IT')
    , ('Manufacturing')
    , ('Sales');


    insert into dbo.Department (DepartmentName, DepartmentNameAlternative, Increment)
    select distinct df.DepartmentName, df.DepartmentName, COALESCE(MAX(Increment), 0) + 1
    from dbo.DailyFile df
    left join dbo.Department d on df.DepartmentName = d.DepartmentName
    where d.DepartmentName is null

DB Fiddle

Answer

Try this:

insert into dbo.Department (DepartmentName, DepartmentNameAlternative, Increment)
SELECT DepartmentName
      ,DepartmentNameAlternative
      ,COALESCE(Increment, 0) + ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM
(
    select distinct df.DepartmentName, df.DepartmentName
    from dbo.DailyFile df
    left join dbo.Department d 
        on df.DepartmentName = d.DepartmentName
    where d.DepartmentName is null
) RW (DepartmentName, DepartmentNameAlternative)
CROSS APPLY
(
    SELECT MAX(Increment)
    FROM Department
) DS (Increment);

The idea is to get the new items, to get the current max increment value and then using row_number to ensure to icrements are unique.