SQL Server INSERT … SELECT Statement won't par

Question

I am getting the following error message with SQL Server 2005

Msg 120, Level 15, State 1, Procedure usp_AttributeActivitiesForDateRange, Line 18 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.

I have copy and pasted the select list and insert list into excel and verified there are the same number of items in each list. Both tables an additional primary key field with is not listed in either the insert statement or select list. I am not sure if that is relevant, but suspicious it may be. Here is the source for my stored procedure:

CREATE PROCEDURE [dbo].[usp_AttributeActivitiesForDateRange]
(
    @dtmFrom DATETIME,
    @dtmTo DATETIME
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @dtmToWithTime DATETIME

    SET @dtmToWithTime = DATEADD(hh, 23, DATEADD(mi, 59, DATEADD(s, 59, @dtmTo)));

    -- Get uncontested DC activities

    INSERT INTO AttributedDoubleClickActivities
        ([Time],
        [User-ID],
        [IP],
        [Advertiser-ID],
        [Buy-ID],
        [Ad-ID],
        [Ad-Jumpto],
        [Creative-ID],
        [Creative-Version],
        [Creative-Size-ID],
        [Site-ID],
        [Page-ID],
        [Country-ID],
        [State Province],
        [Areacode],
        [OS-ID],
        [Domain-ID],
        [Keyword],
        [Local-User-ID],
        [Activity-Type],
        [Activity-Sub-Type],
        [Quantity],
        [Revenue],
        [Transaction-ID],
        [Other-Data],
        Ordinal,
        [Click-Time],
        [Event-ID]) SELECT 
            [Time],
            [User-ID],
            [IP],
            [Advertiser-ID],
            [Buy-ID],
            [Ad-ID],
            [Ad-Jumpto],
            [Creative-ID],
            [Creative-Version],
            [Creative-Size-ID],
            [Site-ID],
            [Page-ID],
            [Country-ID],
            [State Province],
            [Areacode],
            [OS-ID],
            [Domain-ID],
            [Keyword],
            [Local-User-ID]
            [Activity-Type],
            [Activity-Sub-Type],
            [Quantity],
            [Revenue],
            [Transaction-ID],
            [Other-Data],
            REPLACE(Ordinal, '?', '') AS Ordinal,
            [Click-Time],
            [Event-ID]
        FROM Activity_Reports
            WHERE [Time] BETWEEN @dtmFrom AND @dtmTo
            AND REPLACE(Ordinal, '?', '') IN 
        (SELECT REPLACE(Ordinal, '?', '') FROM Activity_Reports 
            WHERE [Time] BETWEEN @dtmFrom AND @dtmTo
        EXCEPT
        SELECT CONVERT(VARCHAR, TripID) FROM VisualSciencesActivities
            WHERE [Time] BETWEEN @dtmFrom AND @dtmTo);

END
GO

Answer 1

You are missing a comma between [Local-User-ID] and [Activity-Type].

Try:

INSERT INTO attributeddoubleclickactivities 
            ([Time], 
             [User-ID], 
             [IP], 
             [Advertiser-ID], 
             [Buy-ID], 
             [Ad-ID], 
             [Ad-Jumpto], 
             [Creative-ID], 
             [Creative-Version], 
             [Creative-Size-ID], 
             [Site-ID], 
             [Page-ID], 
             [Country-ID], 
             [State Province], 
             [Areacode], 
             [OS-ID], 
             [Domain-ID], 
             [Keyword], 
             [Local-User-ID], 
             [Activity-Type], 
             [Activity-Sub-Type], 
             [Quantity], 
             [Revenue], 
             [Transaction-ID], 
             [Other-Data], 
             ordinal, 
             [Click-Time], 
             [Event-ID]) 
SELECT [Time], 
       [User-ID], 
       [IP], 
       [Advertiser-ID], 
       [Buy-ID], 
       [Ad-ID], 
       [Ad-Jumpto], 
       [Creative-ID], 
       [Creative-Version], 
       [Creative-Size-ID], 
       [Site-ID], 
       [Page-ID], 
       [Country-ID], 
       [State Province], 
       [Areacode], 
       [OS-ID], 
       [Domain-ID], 
       [Keyword], 
       [Local-User-ID],
       [Activity-Type], 
       [Activity-Sub-Type], 
       [Quantity], 
       [Revenue], 
       [Transaction-ID], 
       [Other-Data], 
       REPLACE(ordinal, '?', '') AS ordinal, 
       [Click-Time], 
       [Event-ID] 
FROM   activity_reports 
WHERE  [Time] BETWEEN @dtmFrom AND @dtmTo 
       AND REPLACE(ordinal, '?', '') IN (SELECT REPLACE(ordinal, '?', '') 
                                         FROM   activity_reports 
                                         WHERE  [Time] BETWEEN 
                                                @dtmFrom AND @dtmTo 
                                         EXCEPT 
                                         SELECT CONVERT(VARCHAR, tripid) 
                                         FROM   visualsciencesactivities 
                                         WHERE  [Time] BETWEEN 
                                                @dtmFrom AND @dtmTo); 

Answer 2

In the Select there is a typo

[Local-User-ID]
            [Activity-Type],

you are missing ","!

Answer 3

you forgot a comma after [Local-User-ID] so it aliased that column as [Activity-Type]

common mistake

in essence you have Local-User-ID] AS [Activity-Type], the AS is optional

Answer 4

You don't have enough fields in your select list for the insert statement you gave.

Answer 5

You're missing a comma here:

[Local-User-ID] [Activity-Type]

Should be:

[Local-User-ID], [Activity-Type]