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

by

Troubleshooting Guide: Error 8109 – Multiple Identity Columns in SELECT INTO

Why This Error Happens

Error 8109 occurs because SQL Server 2025 does not allow a destination table created via SELECT INTO to contain more than one IDENTITY column. This typically happens when the source query joins two or more tables that each contain an IDENTITY column, and both are implicitly carried into the result set. SQL Server cannot resolve which column should inherit the IDENTITY property when multiple candidates exist, so it halts table creation entirely.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Columns in the Source Query

  • Review the SELECT statement and list every table referenced in the FROM and JOIN clauses.
  • Check each table’s schema using sp_help 'TableName' or query sys.identity_columns to confirm which columns are flagged as IDENTITY.
  • Pay special attention to queries that SELECT * from joined tables, as this often hides the duplicate IDENTITY columns unintentionally.

Step 2: Explicitly Qualify and Reduce Identity Columns in the SELECT List

  • Rewrite the query to select only one IDENTITY column explicitly, converting the second one using a function like CONVERT(int, OtherTable.IDColumn) AS RenamedColumn.
  • Example fix:
  SELECT 
      a.ID AS PrimaryID, 
      CONVERT(int, b.ID) AS SecondaryID, 
      a.Name, 
      b.Description
  INTO NewTable
  FROM TableA a
  JOIN TableB b ON a.ID = b.RefID;
  • This conversion strips the IDENTITY property from the second column while preserving the data values.

Step 3: Validate Table Creation Logic on Windows Server 2025

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Run the corrected script in a test database first to ensure the INTO clause executes without triggering Error 8109.
  • If the issue persists, check for nested views or synonyms in the query that might be re-introducing a hidden IDENTITY column from a different schema path.

Step 4: Use a Pre-Defined Table Structure as a Workaround

  • If SELECT INTO logic is too complex to modify, create the destination table manually using CREATE TABLE with only one IDENTITY(1,1) column defined.
  • Follow this with a standard INSERT INTO NewTable (Columns) SELECT (Columns) FROM Source statement instead of using the INTO auto-create syntax.
  • This bypasses the auto-detection logic that causes Error 8109 entirely.

Alternative or Verification Steps

  • Verification: After running the corrected query, execute SELECT COLUMNPROPERTY(OBJECT_ID('NewTable'), 'ColumnName', 'IsIdentity') on both columns to confirm only one returns a value of 1.
  • Alternative: If you need to preserve both original IDENTITY values for auditing, use CONVERT() on both columns to disable the IDENTITY property entirely for the new table, and manually create a new single auto-incrementing key using ROW_NUMBER() OVER (ORDER BY ...) instead.