在存储过程中使用 insert 语句从一个表中复制一些信息，并从 VBA 插入新信息

Question

我创建一个存储过程，该过程将复制表 1 中的一些列并将其值插入表 2 的新条目中。同时，表 2 上新条目上的 4 列需要使用 VBA 用户窗体上的按钮中的日期、用户名和两个变量进行更新。

我知道如何单独做这些事情，但我不确定如何让它们一起工作。我可以作两句话，但我需要它们都转到表2上的同一条目，而不是两个单独的条目。

以下是我想合并到一个存储过程中的两个语句，以通过单击VBA按钮创建一个新条目。

SET IDENTITY_INSERT [PIA].[dbo].[AttendanceHistory] ON 
INSERT INTO [PIA].[dbo].[AttendanceHistory] (FirstName, LastName, AgentName, Location,
EmployeeGroup, ContractAgency, Manager, Supervisor, Team, Title, Position, 
Staffcimid, FTPT, Bilingual, Five9Email, Email, Weekdayschedule, 
Weekendschedule, CreatedBy, CreatedDate, Exception, Exceptionreason)
SELECT FirstName, LastName, AgentName, Location, EmployeeGroup, ContractAgency, Manager, 
Supervisor,Team, Title, Position, Staffcimid, FTPT, Bilingual, Five9Email, Email, 
Weekdayschedule, Weekendschedule
FROM dbo.Attendance WHERE dbo.Attendance.Agentname = @Agent
Set [CreatedDate] = GETDATE and 
[CreatedBy] = @userid and 
[Exception] = @except and 
[ExceptionReason] = @Exceptreason 

Answer 1

看看这是否有帮助:)

INSERT INTO [PIA].[dbo].[AttendanceHistory] (FirstName,LastName,AgentName,Location,EmployeeGroup,ContractAgency,Manager,Supervisor,Team,Title,Position,Staffcimid,FTPT,Bilingual,Five9Email,Email,Weekdayschedule,Weekendschedule,CreatedBy,CreatedDate,Exception,Exceptionreason)
SELECT FirstName,LastName,AgentName,Location,EmployeeGroup,ContractAgency,Manager,Supervisor,Team,Title,Position,Staffcimid,FTPT,Bilingual,Five9Email,Email,Weekdayschedule,Weekendschedule, GETDATE() , @userid, @except, @Exceptreason
FROM dbo.Attendance WHERE dbo.Attendance.Agentname = @Agent

Answer 2

执行此操作的最佳方法是在 select 子句中传递变量，如下所示：

INSERT INTO [PIA].[dbo].[AttendanceHistory] (FirstName, LastName, AgentName, Location,
EmployeeGroup, ContractAgency, Manager, Supervisor, Team, Title, Position, 
Staffcimid, FTPT, Bilingual, Five9Email, Email, Weekdayschedule, 
Weekendschedule, CreatedBy, CreatedDate, Exception, Exceptionreason)
SELECT FirstName, LastName, AgentName, Location, EmployeeGroup, ContractAgency, Manager, 
Supervisor,Team, Title, Position, Staffcimid, FTPT, Bilingual, Five9Email, Email, 
Weekdayschedule, Weekendschedule, GETDATE() , @userid, @except, @Exceptreason
FROM dbo.Attendance WHERE dbo.Attendance.Agentname = @Agent

GETDATE() , @userid, @except, @Exceptreason与表Attendence的列列表一起添加，从而生成常量字段。