Microsoft SQL Server批量插入报错107095/1070905：需指定所有列

Hey folks, let's unpack these two bulk insert errors you're hitting in SQL Server—they're essentially the same core problem, just wrapped in slightly different error formatting. Let's break down everything you need to know:

What Do These Errors Mean? #

Both errors boil down to the same hard requirement from SQL Server: when performing a bulk insert (via BULK INSERT, bcp utility, SSIS, or other bulk loading tools), every column in the target table must be explicitly accounted for in the operation.

The first error (107095) is a direct, concise message, while the second variant includes the SQL state code 37000 (a general syntax/execution error category) and native error code 1070905—but both are telling you the exact same thing: you're missing one or more columns in your bulk insert setup.

Why Do These Errors Happen? #

Here are the most common triggers:

• You didn't specify all columns in your bulk insert command, and the missing columns don't have a default value, aren't identity (auto-increment) columns, and don't allow NULL values. SQL Server can't guess what value to populate these columns with, so it throws an error.
• Your target table's schema was recently updated (e.g., a new column was added), but your bulk insert script wasn't updated to match the new structure.
• You made a typo in column names or incorrectly mapped source data columns to target table columns, leading SQL Server to think a column is missing.

How to Fix These Errors? #

Try these solutions, ordered by most recommended to most situational:

1. Explicitly Specify All Target Columns #

The most straightforward fix is to ensure your bulk insert operation references every column in the target table. If you're using INSERT ... SELECT with bulk data, list all columns explicitly:

-- Example: Insert from a CSV via OPENROWSET, specifying all columns
INSERT INTO Customer (CustomerID, FirstName, LastName, Email, SignupDate)
SELECT CustomerID, FirstName, LastName, Email, SignupDate
FROM OPENROWSET(
    BULK 'C:\data\customer_data.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2
) AS BulkData;

If you're using the BULK INSERT command directly, use a format file to map every source field to a target column (more on that below), or ensure your source file's columns exactly match the target table's structure (including order and count).

2. Adjust Target Table Column Properties (If Appropriate) #

If some columns don't need to be populated via bulk insert, tweak their properties so SQL Server can auto-fill values:

• Add a default value: For columns like timestamps or status flags, set a default so SQL Server uses it when the column isn't specified:

  ALTER TABLE Customer
  ALTER COLUMN SignupDate DATETIME DEFAULT GETDATE();
  

• Allow NULL values: If the column can be empty temporarily, modify it to accept NULLs:

  ALTER TABLE Customer
  ALTER COLUMN MiddleName VARCHAR(50) NULL;
  

• Enable identity insert (for auto-increment columns): If you need to populate an identity column via bulk insert, toggle identity insert on temporarily:

  SET IDENTITY_INSERT Customer ON;
  -- Run your bulk insert here, including the CustomerID column
  SET IDENTITY_INSERT Customer OFF;
  

3. Update Scripts to Match Schema Changes #

Double-check if the target table's schema was modified recently (e.g., new columns added). If so, update your bulk insert script, format file, or SSIS package to include the new columns.

4. Use a Format File for Complex Mappings #

If your source file's column order doesn't match the target table, or you're using non-standard delimiters, create a format file (.fmt) to explicitly map each source field to a target column. Here's a sample format file snippet:

13.0
5
1       SQLINT        0       4       ","      1     CustomerID           ""
2       SQLCHAR       0       50      ","      2     FirstName            SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50      ","      3     LastName             SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     ","      4     Email                SQL_Latin1_General_CP1_CI_AS
5       SQLDATETIME   0       8       "\r\n"   5     SignupDate           ""

Then reference it in your bulk insert command:

BULK INSERT Customer
FROM 'C:\data\customer_data.csv'
WITH (FORMATFILE = 'C:\data\customer_format.fmt');

内容的提问来源于stack exchange，提问作者Vijitha