How to fix Error 354 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 354 – Nested INSERT Target Cannot Be a View or CTE

Why This Error Happens

This error occurs because SQL Server’s query optimizer does not support a multi-row INSERT statement writing into a view or a Common Table Expression (CTE) when that same statement contains a nested Data Manipulation Language (DML) operation, such as an INSERT...EXEC or a nested INSERT inside the FROM clause. This is a syntactical and architectural limitation rather than a permissions or connectivity issue; the SQL engine cannot resolve the underlying base table reference through the view abstraction layer while simultaneously processing the composable DML nested query. It most commonly occurs when developers attempt to use the OUTPUT INTO clause combined with a nested INSERT targeting a logical view instead of the physical base table.

Step-by-Step Resolution Guide

Step 1: Identify the Nested Query Structure

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
  • Locate the specific Transact-SQL (T-SQL) batch that triggered Error 354 in the Windows Event Viewer (under Applications and Services Logs > MSSQLSERVER).
  • Identify the INSERT statement where the FROM clause contains a composable DML operation (e.g., INSERT ... OUTPUT ... INTO).

Step 2: Redirect the Target to the Base Table

  • Do not point the primary INSERT statement to the View or CTE name; SQL Server 2025 requires a direct reference to the underlying physical table.
  • Modify the query to replace the View name (e.g., dbo.vw_SalesData) with the actual base table (e.g., dbo.tbl_SalesData).
  • Ensure that the column order and constraints of the base table match the structure expected by the nested INSERT command to avoid triggering Error 213 (Column name or number of supplied values does not match).

Step 3: Refactor Using Temporary Staging Tables

  • If the logic strictly requires a View or CTE for filtering, break the composable DML into two distinct steps to avoid the nested conflict.
  • First, execute the nested INSERT...OUTPUT into a temporary table:
  INSERT INTO #TempStaging (ID, Value)
  OUTPUT inserted.ID, inserted.Value
  SELECT ID, Value FROM dbo.SourceTable;
  • Second, run a separate, standard INSERT statement to move data from #TempStaging into the target View or CTE-dependent table.

Step 4: Update SQL Server Database Compatibility Level

  • Open SSMS, right-click the affected database, and select Properties > Options.
  • Verify the Compatibility Level is set to SQL Server 2025 (170).
  • If the compatibility level is outdated (e.g., 140 or 150), older nested DML behaviors might trigger unexpected optimizer conflicts; update it via T-SQL:
  ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • Verification: Re-run the modified T-SQL script in a Test/Staging environment first. If the query executes without throwing Error 354 and the row counts in the target table match the source table, the fix is confirmed.
  • Alternative: If refactoring the query is not immediately possible, encapsulate the nested INSERT logic inside a Stored Procedure and call the procedure using INSERT INTO TargetView EXEC dbo.YourStoredProcedure;, which sometimes bypasses the specific parser restriction on inline nested FROM clauses.