Can anyone explain this error and maybe even suggest how to avoid it?

Hi, I tried to reproduce this issue, and I was able to get the same error in one specific scenario:

the first column in the work table inherited the IDENTITY property from the source table through SELECT INTO.

When that happened, INSERT ... EXEC failed with:

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

When I created the same work table without the inherited IDENTITY property, the problem did not occur.

So my guess is that the issue is not related to NULL values in Column2. The update from NULL to 0 may only have changed the way the statement was compiled or executed, making it look as though nullability was the cause.

It is worth checking the metadata of the work table, especially is_identity:

SELECT
    column_id,
    name,
    TYPE_NAME(user_type_id) AS data_type,
    is_nullable,
    is_identity
FROM sys.columns
WHERE object_id = OBJECT_ID(N'KII.KIIWorkTable')
ORDER BY column_id;

If the first column has is_identity = 1, that would explain the behaviour.

The safest fix is to create the work table explicitly instead of relying on SELECT INTO, so that all column properties are under your control.

here is also a less elegant workaround:

turn the identity column into an expression, for example by adding 0:

SELECT TOP (0)
    Column1 + 0 AS Column1,
    Column2,
    Column3
INTO KII.KIIWorkTable
FROM dbo.maintable;

Below is the full script I used to reproduce the issue and then show the corrected version. I used the AdventureWorks database for the test.

/*
AdventureWorks demo: reproduce error 213 and show the fix

This script contains two independent examples:

PART A - BROKEN
Reproduces:
    Msg 213
    Column name or number of supplied values does not match table definition.

Cause:
SELECT INTO copies the IDENTITY property from SalesOrderID to Column1.
INSERT ... EXEC then receives four values, but the identity column is not
treated as a normal insertable target column.

PART B - FIXED
Creates the work table explicitly, without IDENTITY, and runs the full flow:
    add [$ShardName]
    INSERT ... EXEC
    drop [$ShardName]
    INSERT ... SELECT

Run in an AdventureWorks database containing Sales.SalesOrderHeader.
The script does not modify AdventureWorks source tables.
*/

SET NOCOUNT ON;
SET XACT_ABORT OFF;
GO

/* ============================================================
   STEP 0 - validation and cleanup
   ============================================================ */

IF OBJECT_ID(N'Sales.SalesOrderHeader', N'U') IS NULL
BEGIN
    THROW 50000,
        'Run this script in an AdventureWorks database containing Sales.SalesOrderHeader.',
        1;
END;
GO

DROP PROCEDURE IF EXISTS dbo.DemoRemoteData;

DROP TABLE IF EXISTS dbo.KIIWorkTable_Broken;
DROP TABLE IF EXISTS dbo.KIIWorkTable_Fixed;
DROP TABLE IF EXISTS dbo.DemoTargetTable;
GO

SELECT
    @@SERVERNAME AS ServerName,
    DB_NAME() AS CurrentDatabase,
    CAST(SERVERPROPERTY('ProductVersion') AS varchar(50)) AS ProductVersion;
GO

/* ============================================================
   Shared simulated remote procedure

   A normal local SQL Server instance does not support
   sp_execute_remote. This procedure returns the same shape:
   Column1, Column2, Column3, [$ShardName].
   ============================================================ */

CREATE OR ALTER PROCEDURE dbo.DemoRemoteData
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (100)
        SalesOrderID        AS Column1,
        SalesPersonID       AS Column2,
        PurchaseOrderNumber AS Column3,
        CONVERT(nvarchar(128), N'LocalAdventureWorks') AS [$ShardName]
    FROM Sales.SalesOrderHeader
    ORDER BY SalesOrderID;
END;
GO

/* Confirm that Column2 contains NULL values. */
SELECT
    COUNT(*) AS TotalRows,
    SUM(CASE WHEN SalesPersonID IS NULL THEN 1 ELSE 0 END)
        AS NullSalesPersonRows
FROM Sales.SalesOrderHeader;
GO

/* ============================================================
   PART A - BROKEN EXAMPLE
   Reproduce error 213
   ============================================================ */

/*
SalesOrderID is an IDENTITY column.

Because this SELECT INTO references SalesOrderID directly,
Column1 inherits the IDENTITY property.
*/
SELECT TOP (0)
    SalesOrderID        AS Column1,
    SalesPersonID       AS Column2,
    PurchaseOrderNumber AS Column3
INTO dbo.KIIWorkTable_Broken
FROM Sales.SalesOrderHeader;
GO

ALTER TABLE dbo.KIIWorkTable_Broken
ADD [$ShardName] nvarchar(128) NOT NULL;
GO

/* Check the hidden cause before executing the failing insert.

Expected:
Column1 -> is_identity = 1
*/
SELECT
    N'BROKEN TABLE' AS DemoPart,
    column_id,
    name,
    TYPE_NAME(user_type_id) AS DataType,
    max_length,
    is_nullable,
    is_identity
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.KIIWorkTable_Broken')
ORDER BY column_id;
GO

/*
Expected result:
Msg 213 - Column name or number of supplied values does not match
table definition.

The procedure returns four values:
    Column1, Column2, Column3, $ShardName

But Column1 is IDENTITY and is not treated as a regular insertable
column when no target column list is supplied.
*/
BEGIN TRY
    INSERT INTO dbo.KIIWorkTable_Broken
    EXEC dbo.DemoRemoteData;

    SELECT
        N'BROKEN DEMO' AS DemoPart,
        N'UNEXPECTED PASS' AS Result;
END TRY
BEGIN CATCH
    SELECT
        N'BROKEN DEMO' AS DemoPart,
        N'EXPECTED FAILURE' AS Result,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

/* The table should still be empty. */
SELECT
    N'BROKEN TABLE ROW COUNT' AS CheckName,
    COUNT(*) AS TotalRows
FROM dbo.KIIWorkTable_Broken;
GO

/* ============================================================
   PART B - FIXED EXAMPLE
   Recommended solution: define the work table explicitly
   ============================================================ */

/*
Do not rely on SELECT INTO when exact metadata matters.

The table is defined explicitly:
- no IDENTITY
- known data types
- known nullability
- known position for [$ShardName]
*/
CREATE TABLE dbo.KIIWorkTable_Fixed
(
    Column1     int           NOT NULL,
    Column2     int           NULL,
    Column3     nvarchar(25)  NULL,
    [$ShardName] nvarchar(128) NOT NULL
);
GO

/* Verify that no column is IDENTITY. */
SELECT
    N'FIXED TABLE' AS DemoPart,
    column_id,
    name,
    TYPE_NAME(user_type_id) AS DataType,
    max_length,
    is_nullable,
    is_identity
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.KIIWorkTable_Fixed')
ORDER BY column_id;
GO

/*
Load the simulated remote result.

Using an explicit target column list makes the contract visible.
*/
BEGIN TRY
    INSERT INTO dbo.KIIWorkTable_Fixed
    (
        Column1,
        Column2,
        Column3,
        [$ShardName]
    )
    EXEC dbo.DemoRemoteData;

    SELECT
        N'FIXED INSERT EXEC' AS DemoPart,
        N'PASS' AS Result,
        COUNT(*) AS LoadedRows,
        SUM(CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END)
            AS RowsWithNullColumn2
    FROM dbo.KIIWorkTable_Fixed;
END TRY
BEGIN CATCH
    SELECT
        N'FIXED INSERT EXEC' AS DemoPart,
        N'FAIL' AS Result,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;

    THROW;
END CATCH;
GO

SELECT TOP (10)
    Column1,
    Column2,
    Column3,
    [$ShardName]
FROM dbo.KIIWorkTable_Fixed
ORDER BY Column1;
GO

/* ============================================================
   Continue the original process
   Drop [$ShardName]
   ============================================================ */

ALTER TABLE dbo.KIIWorkTable_Fixed
DROP COLUMN [$ShardName];
GO

SELECT
    N'FIXED TABLE AFTER DROP' AS DemoPart,
    column_id,
    name,
    TYPE_NAME(user_type_id) AS DataType,
    max_length,
    is_nullable,
    is_identity
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.KIIWorkTable_Fixed')
ORDER BY column_id;
GO

/* ============================================================
   Create target table explicitly
   ============================================================ */

CREATE TABLE dbo.DemoTargetTable
(
    Column1 int          NOT NULL,
    Column2 int          NULL,
    Column3 nvarchar(25) NULL
);
GO

/* ============================================================
   Test 1 - original column order
   ============================================================ */

BEGIN TRY
    INSERT INTO dbo.DemoTargetTable
    (
        Column1,
        Column2,
        Column3
    )
    SELECT
        Column1,
        Column2,
        Column3
    FROM dbo.KIIWorkTable_Fixed;

    SELECT
        N'FIXED TEST 1 - ORIGINAL ORDER' AS DemoPart,
        N'PASS' AS Result,
        COUNT(*) AS InsertedRows,
        SUM(CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END)
            AS RowsWithNullColumn2
    FROM dbo.DemoTargetTable;
END TRY
BEGIN CATCH
    SELECT
        N'FIXED TEST 1 - ORIGINAL ORDER' AS DemoPart,
        N'FAIL' AS Result,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

/* ============================================================
   Test 2 - move Column2 to the end
   ============================================================ */

TRUNCATE TABLE dbo.DemoTargetTable;
GO

BEGIN TRY
    INSERT INTO dbo.DemoTargetTable
    (
        Column1,
        Column3,
        Column2
    )
    SELECT
        Column1,
        Column3,
        Column2
    FROM dbo.KIIWorkTable_Fixed;

    SELECT
        N'FIXED TEST 2 - COLUMN2 AT END' AS DemoPart,
        N'PASS' AS Result,
        COUNT(*) AS InsertedRows,
        SUM(CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END)
            AS RowsWithNullColumn2
    FROM dbo.DemoTargetTable;
END TRY
BEGIN CATCH
    SELECT
        N'FIXED TEST 2 - COLUMN2 AT END' AS DemoPart,
        N'FAIL' AS Result,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

/* ============================================================
   Test 3 - replace NULL with 0 and repeat original order
   ============================================================ */

UPDATE dbo.KIIWorkTable_Fixed
SET Column2 = 0
WHERE Column2 IS NULL;

SELECT
    @@ROWCOUNT AS UpdatedRows;
GO

TRUNCATE TABLE dbo.DemoTargetTable;
GO

BEGIN TRY
    INSERT INTO dbo.DemoTargetTable
    (
        Column1,
        Column2,
        Column3
    )
    SELECT
        Column1,
        Column2,
        Column3
    FROM dbo.KIIWorkTable_Fixed;

    SELECT
        N'FIXED TEST 3 - NULL REPLACED WITH 0' AS DemoPart,
        N'PASS' AS Result,
        COUNT(*) AS InsertedRows,
        SUM(CASE WHEN Column2 = 0 THEN 1 ELSE 0 END)
            AS ZeroColumn2Rows
    FROM dbo.DemoTargetTable;
END TRY
BEGIN CATCH
    SELECT
        N'FIXED TEST 3 - NULL REPLACED WITH 0' AS DemoPart,
        N'FAIL' AS Result,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

/* ============================================================
   EXPECTED SUMMARY

   BROKEN DEMO:
   - Column1 has is_identity = 1
   - INSERT ... EXEC returns error 213

   FIXED DEMO:
   - every column has is_identity = 0
   - INSERT ... EXEC passes
   - original order passes
   - reordered columns pass
   - NULL values are accepted
   ============================================================ */

/* ============================================================
   OPTIONAL CLEANUP
   ============================================================ */

/*
DROP PROCEDURE IF EXISTS dbo.DemoRemoteData;
DROP TABLE IF EXISTS dbo.DemoTargetTable;
DROP TABLE IF EXISTS dbo.KIIWorkTable_Fixed;
DROP TABLE IF EXISTS dbo.KIIWorkTable_Broken;
*/