SQL Server INSERT ... SELECT 语句不会解析 [英] SQL Server INSERT ... SELECT Statement won't parse

问题描述

我在使用 SQL Server 2005 时收到以下错误消息

I am getting the following error message with SQL Server 2005

消息 120，级别 15，状态 1，过程 usp_AttributeActivitiesForDateRange，第 18 行INSERT 语句的选择列表包含的项目少于插入列表.SELECT 值的数量必须与 INSERT 列的数量相匹配.

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.

我已将选择列表和插入列表复制并粘贴到 excel 中，并验证每个列表中的项目数量相同.这两个表都没有在插入语句或选择列表中列出附加主键字段.我不确定这是否相关，但可能是可疑的.这是我的存储过程的来源:

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

推荐答案

[Local-User-ID] 和 [Activity-Type] 之间缺少逗号.

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

试试:

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); 

这篇关于SQL Server INSERT ... SELECT 语句不会解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！