SQL Server INSERT ... SELECT Statement won't parse
- by Jim Barnett
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
