Salesforce迁移至SQL Server：保留关联数据与新ID自增方案咨询

Got it, let's break this down step by step—you've got a classic hybrid scenario where you need to preserve legacy Salesforce alphanumeric IDs for relationships, plus add auto-incrementing IDs for new records. I'll cover data types, implementation steps, and even how to mimic Salesforce-style alphanumeric auto-increment if that's what you're after.

1. Data Type Choices #

First, let's lock in the right types for both legacy and new IDs:

• Legacy Salesforce Alphanumeric ID:
  • SQL Server: Use NVARCHAR(18)—Salesforce IDs are either 15 (case-insensitive) or 18 (case-sensitive) characters, so this covers both. Add a UNIQUE constraint to enforce uniqueness for joins.
  • MS Access Frontend: Map this to a Text field with a size of 18. Set it to read-only for new records (since you only need to populate it for migrated data).
• Numeric Auto-Increment ID (for new entries):
  • SQL Server: Go with INT IDENTITY(1,1) for most cases (supports up to ~2 billion records). If you expect massive growth, use BIGINT IDENTITY(1,1) instead. Mark this as your primary key for faster lookups.
  • MS Access Frontend: When linking the SQL Server table, Access will automatically recognize the IDENTITY column as an AutoNumber field. You won't need to manually populate this—SQL Server handles it on insert.

2. Implementation Steps #

SQL Server Backend Setup #

First, modify your existing table to add the auto-increment column and secure the Salesforce ID:

-- Add numeric auto-increment primary key (adjust table/column names to match your setup)
ALTER TABLE YourTableName
ADD NewAutoID INT IDENTITY(1,1) PRIMARY KEY;

-- Add unique constraint to Salesforce ID to ensure no duplicates (critical for joins)
ALTER TABLE YourTableName
ADD CONSTRAINT UQ_YourTable_SalesforceID UNIQUE (Salesforce_ID);

If you already have a primary key on the Salesforce ID, you can skip setting NewAutoID as the PK—just mark it as UNIQUE instead, depending on your relational needs.

MS Access Frontend Configuration #

• Link the SQL Server Table: Use Access's "External Data" > "ODBC Database" to link your SQL Server table. Access will detect the IDENTITY column and treat it as an auto-number field that's read-only for new records.
• Form Setup: For any forms used to create new entries, set the Salesforce_ID field to read-only (or hide it entirely if new records don't need to tie back to Salesforce). The NewAutoID field will auto-populate once the record is saved.

3. Mimicking Salesforce-Style Alphanumeric Auto-Increment #

If your question about "implementing auto-increment for the original alphanumeric primary key" means you want to generate new alphanumeric IDs that follow a similar pattern (instead of numeric auto-increment), here's how to do it in SQL Server:

Option 1: Prefix + Sequential Numeric Suffix (e.g., ACC-0001, ACC-0002) #

This is predictable and easy to maintain:

-- Create a sequence to track the next number
CREATE SEQUENCE Seq_YourTableID
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999 -- Adjust based on your needs
CYCLE;

-- Create a function to generate the formatted ID
CREATE FUNCTION GenerateAlphanumericID()
RETURNS NVARCHAR(10)
AS BEGIN
    DECLARE @NextNum INT = NEXT VALUE FOR Seq_YourTableID;
    -- Customize the prefix and padding here (e.g., 'ACC-' + 6-digit number)
    RETURN 'ACC-' + RIGHT('000000' + CAST(@NextNum AS NVARCHAR(6)), 6);
END;

-- Add the column to your table with a default value
ALTER TABLE YourTableName
ADD NewAlphanumericID NVARCHAR(10) DEFAULT dbo.GenerateAlphanumericID() UNIQUE;

Option 2: Random 18-Character ID (Like Salesforce's Format) #

If you want a random but unique alphanumeric ID similar to Salesforce's 18-character format:

ALTER TABLE YourTableName
ADD NewSalesforceLikeID NVARCHAR(18) DEFAULT LEFT(REPLACE(NEWID(), '-', ''), 18) UNIQUE;

Note: This generates random IDs, not sequential ones. If you need sequential alphanumeric IDs, stick with the sequence-based approach above.

For Access, just set these custom alphanumeric ID fields to read-only—SQL Server will handle generating the value when you insert a new record.

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