Making sure my SQL query is correct

Question

I am trying to run the query below, but somehow I am doing something wrong. I am no expert in SQL Server, but as far as I can see, there is no problem with the code that I am using, but if anyone can see my problem, please point it out to me.

INSERT INTO
    [dbo].[StockItems] ([StockCode],[Description],[Mass],[UnitCost],[MinimumStock])
SELECT DISTINCT
    [s].[New_StockCode],
    RTRIM(LTRIM(ISNULL([s].[New_Description], ''))),
    [s].[Mass],
    CAST([s].[ UnitCost ] AS float),
    CAST([s].[Minimum Stock] AS float)
FROM
    [ExcelItemStock].[dbo].[StockItems$] AS [s]
WHERE
    [s].[New_StockCode] NOT IN (
        SELECT
            [StockCode]
        FROM
            [dbo].[StockItems]
        ) AND
    [s].[New_StockCode] IS NOT NULL AND
    [s].[ UnitCost ] IS NOT NULL AND
    [s].[Minimum Stock] IS NOT NULL

The errors:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Incorrect syntax near the keyword 'AND'.

I am using the same amounts of items in both my INSERT and SELECT. I also cannot see where my problem lies with the AND error?

I am using linq-to-entities and here is my class below if it might help in any way:

public class StockItem
{
    public StockItem()
    {
        SectionGroups = new HashSet<SectionGroup>();
    }
    public int Id { get; set; }


    private string _stockCode;
    [Index(IsUnique = true)]
    [StringLength(450)]
    [Required]
    public string StockCode
    {
        get { return _stockCode; }
        set { _stockCode = value.ToUpper(); }
    }

    public string Description { get; set; }

    public virtual ICollection<SectionGroup> SectionGroups { get; set; }

    public double? Mass { get; set; }

    public double UnitCost { get; set; }

    public double? MinimumStock { get; set; }

    public override string ToString()
    {
        return StockCode + " - " + Description;
    }
}

EDIT: Added the suggested , and getting the error:

Incorrect syntax near 's'.

@Sachu -- Here is a picture to maybe better explain why I am using the StockItems$

I have found my issue! Sorry for wasting all of your time. I forgot to add the extra column to my dbo.StockItems$ table in my ExcelItemStock database. Totally my fault!

Answer 1

Your code is correct except a , between last two values

CAST([s].[ UnitCost ] AS float),
    CAST([s].[Minimum Stock] AS float)

so in Select it will consider only 4 values while in Insert you specified 5 columns

Answer 2

Problem in your SELECT statement:

    CAST([s].[ UnitCost ] AS float)
    CAST([s].[Minimum Stock] AS float)

Missing a common between these lines