SQL Server 2012中如何同步Driver表主键至Driver维度表

Hey there! That error pops up because SQL Server can't tell which table's DriverID you're referencing—both [Driver Table] and [Driver Dimension] have the same column name. Let's fix that and build a reliable sync that works no matter how many new records you add.

Solution 1: Use Table Aliases with NOT IN #

This straightforward query selects all DriverIDs from [Driver Table] that don't already exist in [Driver Dimension] and inserts them:

INSERT INTO [Driver Dimension] (DriverID)
SELECT dt.DriverID
FROM [Driver Table] dt
WHERE dt.DriverID NOT IN (SELECT dd.DriverID FROM [Driver Dimension] dd);

• We use aliases dt for [Driver Table] and dd for [Driver Dimension] to explicitly tell SQL which DriverID we mean.
• The NOT IN clause ensures only new, un-synced records get added.

Solution 2: LEFT JOIN (Safer for NULLs) #

If [Driver Dimension] ever has a NULL value in DriverID, the NOT IN approach might behave unexpectedly. A LEFT JOIN avoids this issue:

INSERT INTO [Driver Dimension] (DriverID)
SELECT dt.DriverID
FROM [Driver Table] dt
LEFT JOIN [Driver Dimension] dd ON dt.DriverID = dd.DriverID
WHERE dd.DriverID IS NULL;

• The LEFT JOIN keeps all records from [Driver Table] and matches them to [Driver Dimension].
• The WHERE dd.DriverID IS NULL filters for records that don't have a match in the dimension table—exactly the new ones you want to sync.

Bonus: Sync Additional Columns (If Needed) #

If you need to sync more than just DriverID, simply add those columns to both the INSERT and SELECT clauses:

INSERT INTO [Driver Dimension] (DriverID, DriverName, HireDate)
SELECT dt.DriverID, dt.DriverName, dt.HireDate
FROM [Driver Table] dt
LEFT JOIN [Driver Dimension] dd ON dt.DriverID = dd.DriverID
WHERE dd.DriverID IS NULL;

Both of these methods will handle any number of new records in [Driver Table]—run them whenever you need to sync, or even set up a scheduled job for automatic syncing!

内容的提问来源于stack exchange，提问作者D.Trump123