SSMS
SSMS
Auto-Increment Import Issue
[1] DANIEL:
I am trying to import data from a flat file back into a table in MS SQL Express using SSMC and a column PortalAliasID which has a Primary key, Int, not null filed. I want the field to auto-increment. In other words - import all the rows from the table, and add the incremented number for the PortalAliasID. The import fails every time, even though I check the box for the column to allow identity insert.
PortalID,HTTPAlias,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate,BrowserType,Skin,CultureCode,IsPrimary 1,albuquerque.pilotadvisors.net,1,2025-11-7 12:47:22.580000000,1,2025-11-7 12:47:22.580000000,Normal,,,False 2,alexandria.pilotadvisors.net,1,2025-11-7 14:48:51.133000000,1,2025-11-7 14:48:51.133000000,Normal,,,False
[2] GROK:
The issue here is a common misunderstanding with how identity columns are handled in the SQL Server Import and Export Wizard. The "Enable identity insert" option (which corresponds to the T-SQL SET IDENTITY_INSERT ON command) is specifically for cases where you want to manually provide your own values for the identity column (PortalAliasID in this case), overriding the auto-increment behavior. When this is enabled and no value is mapped/provided for the column (as is happening here, since your flat file doesn't include a PortalAliasID column), SQL Server attempts to insert NULL, which fails because the column is defined as NOT NULL.
To achieve what you want—importing the data while letting SQL Server auto-generate incrementing values for PortalAliasID—follow these steps in the wizard:
- On the Select Source Tables and Views step, choose your flat file as the source and
dbo.PortalAliasas the destination table.
- Proceed to the Edit Mappings dialog (click "Edit Mappings..." if needed):
- Ensure that PortalAliasID is not mapped to any source column (it should show "<Ignore>" or be unmapped, since your file lacks this column). - Uncheck the "Enable identity insert" checkbox. This tells SQL Server to skip inserting a value for PortalAliasID and instead use its auto-increment logic (assuming the column is defined with IDENTITY(1,1) or similar).
- Review the column mappings to confirm they align with your file's structure:
- PortalID → PortalID - HTTPAlias → HTTPAlias - CreatedByUserID → CreatedByUserID - CreatedOnDate → CreatedOnDate - LastModifiedByUserID → LastModifiedByUserID - LastModifiedOnDate → LastModifiedOnDate - BrowserType → BrowserType - Skin → Skin - CultureCode → CultureCode - IsPrimary → IsPrimary
- If your flat file is CSV or similar, ensure the first row is treated as headers in the source configuration.
- Run the import. The new rows should insert successfully with auto-generated PortalAliasID values starting from the next available seed (e.g., if the table is empty, it starts at 1; otherwise, it continues from the current max + increment).
If the table already has data and you need to reseed the identity (e.g., to start from a specific value), you can do that separately after the import with T-SQL like DBCC CHECKIDENT('PortalAlias', RESEED, 100); (replace 100 with your desired starting value).
If this still fails, double-check the table definition to confirm PortalAliasID is indeed an identity column (SELECT * FROM sys.identity_columns WHERE object_id = OBJECT_ID('dbo.PortalAlias'); should return a row for it). Also, ensure your flat file's date formats match the destination columns (they look fine here, but parsing issues can sometimes masquerade as other errors).