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

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

本文介绍了INSERT INTO SELECT提供:列名或提供的值数与表定义不匹配的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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提供:列名或提供的值数与表定义不匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-30 03:51