在某些情况下从表中逐一读取记录，然后将记录插入到另一个表中 [英] read record one-by-one on some condition from table and insert record into another table

问题描述

hi
每一个，

我有一个表，它的名称是tblRead(源表)，并且此表包含5列(日期，客户，雇员编号，项目，数量，标志)，而目标表是tblwrite，具有相同的结构.

我想从源表中读取记录，并同时检查该记录是否在目标表中可用，还是基于4列(日期，客户，项目，标志)验证，如果记录已在目标表中，则不会插入记录否则记录插入和标志将在源表0中更新为1.


请帮助我

谢谢

hi
every one,

i have table its name is tblRead (source table) and this table contain 5 column (date,customer,employeeNo,item,qty,flag) and destination table is tblwrite with same structure.

i want to read record from source table and check same time this record is available in destination table or not on base of 4 column (date,customer,item,flag)validation, if record is already in destination table then record will be not insert else record insert and flag will be update in source table 0 to 1.


please help me

thanks

推荐答案

您可以使用游标解决上述问题

you can use cursor for the above said problem

Declare @date DateTime,@customer varchar(5),@employeeNo int,@item int,@qty int,@flag varchar(50)
DECLARE @cursor CURSOR FOR
SELECT date,customer,employeeNo,item,qty,flag FROM tblRead

OPEN @cursor

FETCH NEXT FROM @cursor
INTO @date,@customer,@employeeNo,@item,@qty,@flag

WHILE @@FETCH_STATUS = 0
BEGIN
     if((select count(*) from tblwrite where date=@date,customer=@customer,item=@item,flag=@flag)=0)
     begin
          insert into tblWrite(date,customer,employeeNo,item,qty,flag)
          value(@date,@customer,@employeeNo,@item,@qty,@flag)
     end
end

MERGE tblwrite AS w
USING (SELECT * FROM tblRead ) AS r
ON w.employeeNo= r.employeeNo
WHEN  MATCHED THEN
--update statement for source table
WHEN NOT MATCHED THEN
--insert statement for destination table

--------

--------

declare @date datetime
declare @route datetime
declare @employeeNo int


MERGE tblwrite AS w
USING (SELECT * FROM tblRead ) AS r
ON w.employeeNo= r.employeeNo
WHEN  MATCHED THEN
IF EXISTS(SELECT * FROM emplyeetransaction WHERE date=@date and route=#route and employeeno=@employeeno)
--update statement for source table

WHEN NOT MATCHED THEN
--insert statement for destination table

谢谢，
Mamun

Thanks,
Mamun

这是另一种方法

Here is another approach

BEGIN TRY
  BEGIN TRANSACTION

    INSERT INTO tblwrite
    (
      date,
      Customer,
      EmployeeNo,
      Item,
      qty,
      flag
    )
    SELECT R.date, R.Customer, R.EmployeeNo, R.Item, R.qty, R.flag
    FROM tblRead R
    LEFT JOIN tblWrite W ON R.date = W.date AND R.Customer = W.Customer AND R.Item = W.Item AND R.Flag = W.Flag
    WHERE W.EmployeeNo IS NULL AND R.Flag = 0

    --Update the flag in source table after insert
    UPDATE tblRead
    SET Flag = 1
    WHERE Flag = 0

  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
 END CATCH

这篇关于在某些情况下从表中逐一读取记录，然后将记录插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！