INSERT INTO SELECT提供：列名或提供的值数与表定义不匹配 [英] INSERT INTO SELECT gives: Column name or number of supplied values does not match table definition

问题描述

在执行下面的代码时，我收到此错误消息。

I'm getting this error message when executing the code below.

列名或提供的值数与表定义不匹配。

Column name or number of supplied values does not match table definition.

       --DROP TABLE #UniqueBuildingUsageFkAndBuildingFk
         CREATE TABLE #UniqueUsageFkAndBuildingFk (
         Building_fk INT NOT NULL 
         ,Usage_fk INT NOT NULL DEFAULT(0)
         );

         ;WITH UnknownUsageFkAndBuildingFkCTE AS (
        SELECT DISTINCT mm.Building_fk, 0 AS Usage_fk FROM Customers.StandardizedRecord sr
        JOIN Customers.MidasMatch mm ON mm.CustomersBuildingNumberKey = sr.CustomersBuildingNumberKey
        WHERE sr.CustomersBuildingNumberKey NOT IN (SELECT DISTINCT emm.CustomersBuildingNumberKey FROM
        [Customers].[CustomersBuildingNumberKeyCustomersUsageXRef] ebu 
        JOIN [Customers].[MidasMatch] emm ON emm.CustomersBuildingNumberKey = ebu.CustomersBuildingNumberKey WHERE emm.Building_fk > 0)
        AND mm.Building_fk > 0 
        ) 
        INSERT INTO #UniqueUsageFkAndBuildingFk 
        SELECT Building_fk, Usage_fk
        FROM UnknownUsageFkAndBuildingFkCTE;

但是当我注释掉INSERT INTO #UniqueUsageFkAndBuildingFk行时，我将返回所需的结果集。

But when I comment out the INSERT INTO #UniqueUsageFkAndBuildingFk line I'm returning the desired resultset.

我迷路了...

推荐答案

在您的表中 #UniqueUsageFkAndBuildingFk ，您有3列（ Building_fk，Usage_fk，CustomersBuildingNumberKey ）。因此，在插入此表时，您必须为这三列指定值

In your table #UniqueUsageFkAndBuildingFk you have 3 columns (Building_fk, Usage_fk, CustomersBuildingNumberKey). So while insert into to this table you have to specify values for these three column

INSERT INTO #UniqueUsageFkAndBuildingFk 
SELECT Building_fk, Usage_fk, 0
FROM UnknownUsageFkAndBuildingFkCTE;

或者您可以在插入内容中指定列

or you can specify the column in your insert

INSERT INTO #UniqueUsageFkAndBuildingFk (Building_fk, Usage_fk)
SELECT Building_fk, Usage_fk
FROM UnknownUsageFkAndBuildingFkCTE;

这篇关于INSERT INTO SELECT提供：列名或提供的值数与表定义不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！