为什么当我在SQL Server做SELECT..INSERT做我得到这个错误
问题描述:
为什么我得到这个错误为什么当我在SQL Server做SELECT..INSERT做我得到这个错误
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
当我运行此查询
INSERT INTO TempOutputOfGroupifySP
(MonthOfQuery,Associate,[NoOfClaims],[ActualNoOfLines],[AverageTATInDays],
[NoOfErrorsDiscovered],[VarianceinPercent],[NoOfClaimsAudited],[InternalQualInPercent],[ExternalQualInPercent]
)
SELECT (DATENAME(MONTH,[ClaimProcessedDate])) AS MonthOfQuery,
Temp.Associate AS Associate,
COUNT(*) AS [NoOfClaims],
SUM(NoOfLines) AS [ActualNoOfLines] ,
(SUM(DATEDIFF(dd,[ClaimReceivedDate],[ClaimProcessedDate]))/COUNT(*)) AS [AverageTATInDays],
A.[NoOfErrorsDiscovered] AS [NoOfErrorsDiscovered],
Temp.[MonthlyTarget] As [TargetNoOfLines],(Temp.[MonthlyTarget] - COUNT(*)) AS [VarianceInPercent],
B.[NoOfClaimsAudited] AS [NoOfClaimsAudited],
((A.[NoOfErrorsDiscovered]/NULLIF(B.[NoOfClaimsAudited],0))*100) AS [InternalQualInPercent],
NULL AS [ExternalQualInPercent]
FROM
(SELECT COUNT(*) AS [NoOfErrorsDiscovered] FROM TempTableForStatisticsOfAssociates T1 WHERE [TypeOfError] IS NOT NULL) AS A,
(SELECT COUNT(*) AS [NoOfClaimsAudited] FROM TempTableForStatisticsOfAssociates T2 WHERE Auditor IS NOT NULL) AS B,
TempTableForStatisticsOfAssociates Temp
GROUP BY DATENAME(MONTH,([ClaimProcessedDate])),
Temp.Associate,
A.[NoOfErrorsDiscovered],
Temp.[MonthlyTarget],
B.[NoOfClaimsAudited]
目标表的结构是
CREATE TABLE [dbo].[TempOutputOfGroupifySP](
[MonthOfQuery] [nchar](10) NULL,
[Associate] [nvarchar](max) NULL,
[NoOfClaims] [int] NULL,
[ActualNoOfLines] [int] NULL,
[AverageTATInDays] [int] NULL,
[NoOfErrorsDiscovered] [int] NULL,
[VarianceInPercent] [float] NULL,
[NoOfClaimsAudited] [int] NULL,
[InternalQualInPercent] [float] NULL,
[ExternalQualInPercent] [float] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
答
您的INSERT INTO
为插入定义了10个列,但是,您的SELECT
语句会返回11列。您要么在INSERT
声明中缺少一列,要么在您的SELECT
声明中返回太多。 比较你的表结构和您的SELECT
和INSERT
在SELECT
声明下面的行不具有对应:
Temp.[MonthlyTarget] As [TargetNoOfLines]
这可能是因为你已经在你的'INSERT'声明10个colomns并返回11列在您的'SELECT'语句中... – 2014-09-03 08:04:44
您正在选择11项并仅插入9项 – Amit 2014-09-03 08:05:37
对不起,我意识到我错过了TargetNoOfLines。谢谢。 – Chakra 2014-09-03 08:05:56