在SQL Server中插入记录时更新列

I have a table Conservation_Dev with these columns (amongst 30 other):

我有一个表Conservation_Dev与这些列(其他30个):

I3_IDENTITY - a BIGINT and a unique key

I3_IDENTITY - BIGINT和唯一键

STATE - a 2-letter US state abbreviation

STATE - 两个字母的美国州名缩写

ZONE - when I want to store the time zone for this record

区域 - 当我想存储此记录的时区时

I also have a table TimeZoneCodes that maps US states to time zones (forget the fact that some states are in more than one time zone):

我还有一个表TimeZoneCodes,它将美国州映射到时区(忘记了一些州位于多个时区的事实):

state_code - the 2-letter abbreviation for the state

state_code - 状态的2个字母缩写

time_zone - the text with the time zone (EST, CST, etc)

time_zone - 包含时区的文本(EST,CST等)

I have data being loaded into Conservation_Dev without the time zone data and that is something that I can't control. I want to create an after insert trigger that updates the record. Doing some research on previous threads I came up with the following:

我将数据加载到Conservation_Dev而没有时区数据,这是我无法控制的。我想创建一个更新记录的后插入触发器。对以前的线程做了一些研究,我想出了以下内容:

CREATE TRIGGER [dbo].[PopulateTimeZoneBasedOnUSState]
ON [dbo].[Conservation_Dev]
AFTER INSERT
AS 
BEGIN   
    UPDATE [dbo].[Conservation_Dev]
    SET [ZONE] = (SELECT [time_zone] 
                  FROM [dbo].[TimeZoneCodes] Z 
                  WHERE Z.[state_code] = i.[STATE])
    FROM Inserted i
    WHERE [I3_IDENTITY] = i.[I3_IDENTITY]
END

I get an error, though:

但是我收到了一个错误:

Ambiguous column name 'I3_IDENTITY'

不明确的列名'I3_IDENTITY'

Also, is this the right way to do this? Will this be a problem if the data load is, say, 5 or 10 thousand records at a time through an SSIS import package?

另外,这是正确的方法吗?如果通过SSIS导入包一次数据加载是5或10,000条记录,这会成为一个问题吗?

2 个解决方案

#1

My vote would be to move this update into the SSIS package so that the insert of data is all in one place. I may then move to a stored procedure that runs after the data is loaded. A trigger is going to happen on every insert. I think table based queries/updates would have a better performance. Triggers can be hard to find from a troubleshooting standpoint, but if they are well documented then it may not be much of an issue. The trigger will allow the Zone to be populated once a record is inserted.

我的投票是将此更新移动到SSIS包中,以便数据插入在一个地方。然后,我可以移动到加载数据后运行的存储过程。每次插入都会发生触发。我认为基于表的查询/更新会有更好的性能。从故障排除的角度来看很难找到触发器,但是如果它们有详细记录,那么它可能不是什么大问题。插入记录后,触发器将允许填充区域。

The problem with the trigger you are creating is because SQL Server doesn't know which I3_IDENTITY you are referring to in the first part of the WHERE clause. This should fix it:

您正在创建的触发器的问题是因为SQL Server不知道您在WHERE子句的第一部分中引用了哪个I3_IDENTITY。这应该解决它:

CREATE TRIGGER [dbo].[PopulateTimeZoneBasedOnUSState]
ON [dbo].[Conservation_Dev]
AFTER INSERT
AS 
BEGIN   
    UPDATE [dbo].[Conservation_Dev]
    SET [ZONE] = (SELECT TOP 1 [time_zone] FROM [dbo].[TimeZoneCodes] Z WHERE Z.[state_code] = i.[STATE])
    FROM Inserted i
    WHERE [dbo].[Conservation_Dev].[I3_IDENTITY] = i.[I3_IDENTITY]
END

This would be a table level update that will update all time_zones in one sweep. I would use something like this in a stored procedure after the initial inserts were complete.

这将是一个表级更新,它将在一次扫描中更新所有time_zones。在初始插入完成后,我会在存储过程中使用类似的东西。

CREATE PROCEDURE dbo.UpdateAllZones   
AS 
 Update dbo.Conservation_Dev
 SET Zone = Time_Zone
 From dbo.Conservation_Dev c INNER JOIN dbo.TimeZoneCodes z ON c.state_code = z.state_code
Go

Executed as EXEC dbo.UpdateAllZones

执行为EXEC dbo.UpdateAllZones

#2

Try:

CREATE TRIGGER [dbo].[PopulateTimeZoneBasedOnUSState]
ON [dbo].[Conservation_Dev]
AFTER INSERT
AS 
BEGIN   
    UPDATE A
    SET A.[ZONE] = Z.[time_zone]
    FROM [dbo].[Conservation_Dev] as A
    INNER JOIN Inserted as i
    ON A.[I3_IDENTITY] = i.[I3_IDENTITY]
    INNER JOIN [dbo].[TimeZoneCodes] as Z 
    ON Z.[state_code] = i.[STATE]
END

#1