[Database]AI-Assisted Migration to SQL Server

Summary: Let AI lend a hand and migrate your Access/Excel data into SQL Server reliably.

💡 This article is L4 (Advanced Hands-On). It assumes you already know how to build tables in Access and roughly know what SQL is (if not, catch up with B13 "Designing Your First Access Table with AI", B14 "Writing Query SQL with AI — No Prior Experience Required", and B15 "Designing Normalized Database Structures with AI"). Now let's run a real migration end to end.

1. Pain Points: Why Migrate to SQL Server

Have you ever hit any of these headaches?

Excel files balloon to several dozen megabytes; opening them just spins a wheel, and once formulas multiply, the whole thing freezes.
An Access database tops out at 2 GB per file. Once customer data piles up, you get "Database has reached size limit", and a pop-up might even say "Database has been corrupted, attempting repair".
Your boss wants a reporting system and a BI dashboard, but Excel/Access can't handle simultaneous reads and writes from many users — the moment two people edit, they overwrite each other.

That's when moving your data into SQL Server becomes a solid answer. SQL Server is Microsoft's "proper database" — like Access, it stores tables, but it's far more stable, scales much larger, and supports many concurrent users. It also has a free edition called Express, which is more than enough for a small team.

But the moment you actually try to migrate, new questions pop up: How do I build the tables? What is Access's "AutoNumber" called in SQL Server? Will dates and amounts from Excel get mangled? Will the relationships between tables — for example, "one customer has many orders" — survive the move?

Don't panic. Let ChatGPT be your "migration assistant": let it draft the CREATE TABLE SQL, translate the type mappings, write the validation queries — while you steer the direction and sign off at the end. Below, we'll run the whole migration using the most common one-to-many pattern: a Customers table plus an Orders table.

2. What You'll Get

By the time you finish the walkthrough, you'll have:

A SQL Server database (Express or any other edition will both work) holding two tables: Customers and Orders;
Sensible table design: correct column types, a primary key (Primary Key — the unique "ID card" of every row), and a foreign key (Foreign Key — guarantees that an order's CustomerID actually points to a real customer);
Data moved from Access/Excel as-is, then verified by reconciliation — row counts match and no content is lost;
A reusable set of "AI prompt + SQL templates" you can copy for the next table.

3. Hands-On — Migrating Customer/Order Data to SQL Server

We'll follow the actual operation sequence. Every SQL snippet below can be pasted straight into SSMS (SQL Server Management Studio, Microsoft's official database management tool) and run.

Step 1: Install the Driver So SSMS Can "Read" Your Source File

SSMS's Import Wizard doesn't recognize Access/Excel file formats on its own. It depends on a component called the ACE driver (Access Database Engine — Microsoft's "Access/Excel card reader") to read those files.

Go to the Microsoft site and search for "Microsoft Access Database Engine", then download and install it.
Critical pitfall: The ACE driver comes in both 32-bit and 64-bit. What matters is the bitness of the import wizard process, not your Office. The "Import and Export Data" wizard bundled with SSMS is itself a 32-bit process. So even if you've installed 64-bit Office, you still need the 32-bit ACE driver; otherwise the wizard can't open your file. Pairing 32-bit Office with 64-bit ACE (or vice versa) will also fail. Before installing, check whether your Office is 32-bit or 64-bit — the driver's bitness should follow the wizard process. One more classic roadblock: if the machine already has 64-bit Office installed, the 32-bit ACE installer will refuse to install outright — work around it with a silent command-line install (AccessDatabaseEngine.exe /quiet), or switch to the standalone 64-bit "SQL Server Import and Export" wizard in the Start menu instead.

Step 2: Let ChatGPT Draft the CREATE TABLE SQL

Don't try to write SQL from scratch. Paste your Access/Excel table structure into ChatGPT and ask it to translate that into SQL Server's CREATE TABLE statements.

Ready-to-use prompt:

我有两个 Access 表，请生成 SQL Server 的建表 SQL，并把 Access 类型正确映射成 SQL Server 类型，补上主键和外键，附中文注释。

Customers（客户表）：客户ID 自动编号、客户名称文本(100)、电话文本(20)、录入时间日期/时间

Orders（订单表）：订单ID 自动编号、客户ID 长整型、下单时间日期/时间、金额货币、备注长文本

ChatGPT will generally spit out something like the SQL below (I've added comments so you can follow along):

sql
-- 客户表：对应 Access 里的 Customers
CREATE TABLE dbo.Customers (
    CustomerID   int            IDENTITY(1,1) NOT NULL,  -- 自增主键，对应 Access 的"自动编号(AutoNumber)"
    CustomerName nvarchar(100)  NOT NULL,                -- 客户名称，对应 Access 的"文本(Text)"
    Phone        nvarchar(20)   NULL,                    -- 电话
    CreatedAt    datetime       NOT NULL DEFAULT GETDATE(), -- 录入时间，对应 Access 的"日期/时间(Date/Time)"
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)      -- 主键：每一行客户的唯一身份证
);
GO

-- 订单表：对应 Access 里的 Orders
CREATE TABLE dbo.Orders (
    OrderID    int             IDENTITY(1,1) NOT NULL,  -- 订单号，自增主键
    CustomerID int             NOT NULL,                -- 客户编号，外键，指回 Customers
    OrderDate  datetime        NOT NULL,                -- 下单时间
    Amount     decimal(19,4)   NOT NULL,                -- 金额，对应 Access 的"货币(Currency)"
    Remark     nvarchar(max)   NULL,                   -- 备注，对应 Access 的"长文本(Memo)"
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID)
);
GO

Type Mapping Cheat Sheet — the core of what AI translates (memorize this):

Access data type	SQL Server data type	Notes
Text (short)	`nvarchar(n)`	n is the max length, e.g. 50, 100
Long Text (Memo)	`nvarchar(max)`	max means "fits anything long"
AutoNumber	`int IDENTITY(1,1)`	Auto-incrementing integer; +1 on every insert
Number (Integer / Long Integer)	`int`	Integer
Currency	`money` or `decimal(19,4)`	Money; four-decimal precision is more stable
Date/Time	`datetime` or `datetime2`	Date plus time
Yes/No	`bit`	Stores 0/1 (No/Yes)
OLE Object / Attachment	`varbinary(max)`	Binary; consider storing images/attachments as files instead

Step 3: Create the Database and Tables in SSMS

Open SSMS and connect to your SQL Server instance (usually (local) or . on your own machine);
In Object Explorer, right-click Databases → New Database, and name it ShopDB;
Expand ShopDB → right-click New Query, paste the CREATE TABLE SQL from Step 2, and click Execute. The two empty tables are now created.

Step 4: Use the Import/Export Wizard to Move Data Over (Don't Forget a Reconciliation Copy)

⚠️ Top Priority (Read This First): In this case, both Customers.CustomerID and Orders.CustomerID are identity columns (IDENTITY). The primary key / foreign key across both tables must be preserved together or renumbered together — either enable "Enable Identity Insert" on both so the source IDs are kept, or let SQL Server reissue new IDs on both. Mixing the two (for example, letting SQL Server reissue new IDs on Customers while keeping the old IDs on Orders) leaves Orders.CustomerID with no matching row in Customers, and the ALTER TABLE ... FOREIGN KEY in Step 5 will fail. The steps below stick to "keep source IDs"; be sure to tick "Enable identity insert" wherever prompted.

For the EXCEPT reconciliation in Step 6 to be meaningful, Orders must be imported twice: once into Orders_Src (a verbatim copy that acts as the "answer key"), once into the production Orders table. Both copies must keep the same source OrderID.

In Object Explorer, right-click ShopDB → Tasks → Import Data (this opens the SSMS Import/Export Wizard);
Click "Next" on the welcome page;
For the Data Source, pick "Microsoft Access" (or "Microsoft Excel" for an Excel source) and select your .accdb or .xlsx file;
- If you get an error like "provider not registered / driver not found", head back and re-check the ACE driver from Step 1;
For the Destination, choose SQL Server (such as "Microsoft OLE DB Driver for SQL Server" or "SQL Server Native Client"). Fill in your server instance and pick ShopDB;
Choose "Copy data from one or more tables or views" → Next;
Import Customers: tick Customers, click "Edit Mappings" to verify each column's type matches what you set in Step 2 (e.g., the name is nvarchar(100), not nvarchar(255)), and tick "Enable identity insert" to preserve the source CustomerID (otherwise SQL Server will reassign new IDs, mismatching the source CustomerIDs kept on Orders in Step 8 and breaking the FK in Step 5). Since the empty tables are already in place, choose "Append to an existing table" here → Next → Run immediately → Finish.
Import the reconciliation copy Orders_Src (Create destination table + preserve source OrderID): run the wizard again (repeat steps 1–5), tick only Orders. On the mappings page, pick "Create destination table" so the wizard creates a brand-new Orders_Src, then click "Edit Mappings" and tick "Enable identity insert" to preserve the original OrderID from Access → Run immediately → Finish. Orders_Src is now a "verbatim" source copy.
Import the production table Orders (Append to existing table + preserve source OrderID): run the wizard a third time (repeat steps 1–5), tick only Orders, and this time select "Append to an existing table" so rows land in the Orders table built in Step 3. Again click "Edit Mappings" and tick "Enable identity insert" to preserve the source OrderID → Run immediately → Finish. By now, Orders and Orders_Src share identical OrderIDs — which is what makes the EXCEPT comparison in Step 6 meaningful.

Under the hood, the wizard is just Microsoft's SSIS (SQL Server Integration Services, a data-movement engine) reading the source file row by row and writing into the destination table.

Step 5: Add the Foreign Keys (the Wizard Doesn't Carry Relationships Over)

The Import Wizard only moves "data" — it does not move relationships between tables. So after the import, you still need to add the foreign keys yourself, telling the database "every CustomerID in Orders must point to a real customer".

Before you proceed, confirm that every CustomerID in Orders can be found in Customers; otherwise the ALTER TABLE below will fail outright with a foreign-key conflict. Quick check: SELECT CustomerID FROM dbo.Orders WHERE CustomerID NOT IN (SELECT CustomerID FROM dbo.Customers).

Either ask ChatGPT for the snippet, or copy this:

sql
-- After the data is in, add the foreign key: one customer can have many orders
ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID);
GO

After running this, if you try to insert an Order with a CustomerID that doesn't exist, SQL Server will reject it outright — that's the foreign key keeping your data clean.

Step 6: Post-Migration Reconciliation

Don't celebrate yet — reconcile first. Three short queries and you're done:

sql
-- 1) Row-count reconciliation: see how many rows each table has on the SQL side
SELECT 'Customers' AS 表名, COUNT(*) AS 行数 FROM dbo.Customers
UNION ALL
SELECT 'Orders', COUNT(*) FROM dbo.Orders;

-- 2) Data comparison: compute the difference between the freshly imported Orders
--    and the "source-copy of record" Orders_Src
--    Result rows = mismatches; empty result = perfect match
SELECT * FROM dbo.Orders
EXCEPT
SELECT * FROM dbo.Orders_Src;   -- Orders_Src is the "reconciliation copy" imported as a brand-new table in Step 4; it shares the same OrderIDs as Orders

-- 3) Eyeball the first 100 rows for a quick sanity check
SELECT TOP (100) * FROM dbo.Orders ORDER BY OrderID;

⚠️ Iron Rule of Reconciliation: Both Orders and Orders_Src must preserve the same source OrderID (you enabled identity insert on both in the "Edit Mappings" page). If either side lets SQL Server reassign IDs, the OrderIDs drift apart, EXCEPT spuriously reports "mismatch", and the entire reconciliation result is void.

4. How It Works

One sentence to describe the whole migration: under the hood, the SSMS Import/Export Wizard is powered by SSIS — a "data-movement engine" — which translates each column of your Access/Excel source file into a SQL Server type via the "type mapping table", and writes the data row by row into the destination table. The "type mapping" is essentially a translation dictionary between Access and SQL Server (Text → nvarchar, AutoNumber → int IDENTITY, Currency → money/decimal(19,4)). Rules such as primary keys and foreign keys live at the logical layer; the wizard doesn't carry them over by default, so you have to add them yourself afterwards with ALTER TABLE. The final reconciliation step is, in essence, a "row-by-row subtraction" between the SQL-side table and the source (EXCEPT); only an empty difference set proves that not a single row went missing and not a single character was mangled.

5. Common Pitfalls

ACE driver bitness mismatch: the bitness of the import wizard process is what counts — the "Import and Export Data" wizard bundled with SSMS is a 32-bit process. So even if you've installed 64-bit Office, you still need the 32-bit ACE driver; otherwise the wizard can't open your file. Pairing 32-bit Office with 64-bit ACE (or vice versa) trips the same trap. Check whether your Office is 32-bit or 64-bit before installing, and match the driver to the wizard process.
Excel types thrown off by the "first few rows": When importing Excel, the wizard only inspects the first few rows by default to guess whether a column is numeric or text. If the first 8 rows are pure numbers and row 9 suddenly contains Chinese characters, the entire column gets misclassified as numeric and the text gets silently dropped. Fixes: add IMEX=1 to the connection string (force the whole column to be read as text), save the Excel as "Text" first, or route the file through Power Query for cleanup before it reaches SQL Server.
The wizard doesn't move primary keys, foreign keys, or indexes: it only carries the data over; you have to add table relationships manually with ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY. Skip this and you'll soon see "ghost customer IDs" in your orders — and the mess will only compound from there.
Identity-column (AutoNumber) imports go wrong: SQL Server's identity columns (IDENTITY) don't accept externally supplied values by default. If you want to preserve the original IDs from Access exactly, tick "Enable identity insert" on the wizard's "Edit Mappings" page. If you don't care about the old IDs and want SQL Server to reissue new ones, drop that column from the mapping. The primary/foreign keys across the two tables must be preserved together or renumbered together — never keep old IDs on one and issue new on the other, or the FK won't match.
OLE Object / Attachment data tends to get lost or mangled: Images and file attachments stored in Access either fail to import or come through as gibberish varbinary(max) blobs. The proper approach: keep attachments in a folder or object store, and only store a "file path" column in the database. Don't expect the wizard to clean up your images.

6. Going Further

Fill in the basics first: This article's CREATE TABLE statements, type choices, and foreign keys build on B13 "Designing Your First Access Table with AI", B14 "Writing Query SQL with AI — No Prior Experience Required", and B15 "Designing Normalized Database Structures with AI". If you're not yet fluent in table design or SQL queries, go back and read those first.
Hook reports straight onto the migrated data: Once the data is in SQL Server, you can use Excel's Power Query to clean and reshape it and a PivotTable to summarize it; behind the scenes, multi-table joins boil down to SQL's JOIN. For richer metrics, move on to DAX (Data Analysis Expressions). That thread is developed further in the Excel/Power series.
Use the official tool for larger migrations: When there are many tables and you also need to carry over queries and form logic, don't wrestle the wizard — Microsoft offers a free SSMA for Access (SQL Server Migration Assistant) that bulk-migrates schemas, data, and parts of your queries, far more reliably than clicking through the wizard by hand.
Keep letting AI help: Once the tables grow, let ChatGPT write indexes (to speed up queries) and stored procedures (to package routine operations as one-click scripts). Keep handing the repetitive work to AI.

🛠️ AI-Assisted Migration to SQL Server