如何使用cursor和trigger togather在SQL中更新table1中的table1 [英] How to update table1 from table2 in SQL using cursor and trigger togather

问题描述

我在asp.net中工作，希望将记录从一个表更新到另一个表。假设用户将他们的出勤数据放在excel文件中。我做了一个浏览表单，用户将上传excel文件。它运行良好，并将数据从excel存储到SQL数据库。 （表名= Import_Attendance_imp）

现在我想更新另一个表，即TblDailyAttandence

我正在使用触发器，但它只更新了一行，我认为应该有用光标

我不知道光标如何在触发器中使用光标。



请帮帮我



提前谢谢



我尝试过：



ALTER TRIGGER [dbo]。[TR​​IG_Update_Attendance_imp]

ON [dbo]。[Import_Attendance_imp]

插入后



AS



BEGIN

DECLARE

@EmpCode VARCHAR（100），

@Monthyear VARCHAR（100），

@AtCol VARCHAR（100），

@Status VARCHAR（100），

@in VARCHAR（100），

@inTime VARCHAR（100），

@out VARCHAR（100），

@outTime VARCHAR（ 100）



SELECT @AtCol = Att，@ in = [in]，@ inTime = inTime，@ out = [out]，@ outTime = outTime，@ EmpCode = empcod e，@ Status = [status]，@ Monthyear = Monthyear FROM INSERTED

DECLARE @query VARCHAR（MAX）=''



SET @query ='UPDATE TblDailyAttandence SET'+ @ AtCol +'='''+ @ Status +'''，'+ @ in +'='''+ @ intime +'''，'+ @ out +'='''+ @ outtime +'''

WHERE EmpCode ='''+ @ EmpCode +'''AND Monthyear ='''+ @ Monthyear +''''



PRINT @query



EXECUTE（@query）



END

I am working in asp.net want to update records from one table to another table. Suppose user have their attendance data in a excel file. I made a browse form from where user will upload excel file . It working very well and storing data from excel to SQL Database. (Table name= Import_Attendance_imp)
Now I want to update another table where i.e TblDailyAttandence
I am using trigger but it updating only one row of, I think there should be use cursor
I have not idea about cursor how to use cursor in my trigger.

please help me

thank in advance

What I have tried:

ALTER TRIGGER [dbo].[TRIG_Update_Attendance_imp]
ON [dbo].[Import_Attendance_imp]
AFTER INSERT

AS

BEGIN
DECLARE
@EmpCode VARCHAR(100),
@Monthyear VARCHAR(100),
@AtCol VARCHAR(100),
@Status VARCHAR(100),
@in VARCHAR(100),
@inTime VARCHAR(100),
@out VARCHAR(100),
@outTime VARCHAR(100)

SELECT @AtCol = Att,@in=[in],@inTime=inTime,@out=[out],@outTime=outTime, @EmpCode = empcode, @Status = [status],@Monthyear=Monthyear FROM INSERTED
DECLARE @query VARCHAR(MAX) = ''

SET @query = 'UPDATE TblDailyAttandence SET '+@AtCol+' = '''+@Status+''','+@in+' = '''+@intime+''','+@out+' = '''+@outtime+'''
WHERE EmpCode = '''+@EmpCode+''' AND Monthyear='''+@Monthyear+''''

PRINT @query

EXECUTE(@query)

END

推荐答案

游标会降低性能，在您的情况下，更新记录的数量仅减少为1，因为您通过变量传输值，这会减少对临时<$的读取操作c $ c> INSERTED 表到第一条记录。要解决您的问题，只需直接从 INSERTED 加入并分配 UPDATE 语句中所需的值，它就可以正常工作。做这样的事情：

A cursor would slow down performance and in your case the count of updated records is only reduced to one because you're transporting the values via variables which reduces the read operation on the temporary INSERTED table to the first record. To resolve your problem simply join and assign the values needed in the UPDATE statement directly from INSERTED and it will work. Do something like this:

UPDATE a
   SET a.[Field1] = b.[Field1]
     , a.[Field2] = b.[Field2]
     , ...
  FROM [dbo].[TblDailyAttandence] a
   INNER JOIN inserted b ON b.[Empcode] = a.[empcode] AND b.[Monthyear] = a.[MonthYear]

这篇关于如何使用cursor和trigger togather在SQL中更新table1中的table1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！