合并表（添加，更新和删除不同来源的记录） [英] Merge tables(add, update and delete records from different sources)

问题描述

我有三个表tb1，tb2和tbTotal。他们有相同的模式。这些表有三列，MetricID，Descr和EntryDE。

我想要的是将tb1与tbTotal合并。我已经做到了，它的罚款。
我的存储过程是：

  CREATE PROCEDURE [dbo]。[Admin_Fill] 
  - 添加这里存储过程的参数
 @MetricId INT，
 @Descr VARCHAR（100），
 @EntryDE VARCHAR（20）
 AS 
 
 BEGIN 
  - 添加SET NOCOUNT ON以防止
中的额外结果集 - 干扰SELECT语句。 
 SET NOCOUNT ON; 
 --SET IDENTITY_INSERT dbo.tbTotal ON 
  - 在此处插入语句
; WITH cte AS（SELECT MetricId = @ MetricId，Descr = @ Descr，EntryDE = @ EntryDE）
 MERGE tbTotal d 
 USING cte s 
 ON s.EntryDE = d.EntryDE 
 AND s.MetricId = d.MetricId 
匹配THEN UPDATE 
设置MetricId = s.MetricId，
 Descr = s.Descr，
 EntryDE = s.EntryDE 
何时未匹配BY TARGET THEN 
 INSERT（MetricId，Descr，EntryDE）
 VALUES（s.MetricId，s.Descr，s.EntryDE）; 
 END 
  

我的C＃代码：

// $ dat $ t
{
MetricId = Convert.ToInt32（row [MetricId]）;
Descr = row [Descr]。ToString（）;
EntryDE = row [EntryDE]。ToString（）;
parameters.Add（@ MetricId，MetricId）;
parameters.Add（@ Descr，Descr）;
parameters.Add（@ EntryDE，EntryDE）;
dbaccess.ExecuteNonQuery（strStoredProcedure，parameters）; //cmd.ExecuteNonQuery（）;
parameters.Clear（）;
}

此外，我想从dtTotal中删除dt2中的所有记录。我不知道如何修改存储过程。

感谢您的帮助。

解决方案

如果我已经明白你正在做的正确，那么这可能是我更喜欢实现该解决方案的。

我将2个数据表作为TABLE变量传递给SP，类似于下面，然后使用SET操作根据需要使用JOIN进行UPDATE和DELETE，从而影响多行在一个查询中，并避免分别循环遍历每一行。

如AdaTheDev所述，您将最终创建一个TABLE类型，但没有一个额外的类型的缺点，这个解决方案将比

免责声明： - 以下代码可能无法在句法上正确，但我希望您能够了解我提出的内容。

  CREATE TYPE TableType AS TABLE 
（
 MetricId INT，
描述VARCHAR（300） - 无论适用的长度，
 EntryDE INT 
）; 
 GO 
 
 CREATE PROCEDURE [dbo]。[Admin_Fill] 
 @RowsForUpdate TableType READONLY，
 @RowsForDelete TableType READONLY 
 AS 
 BEGIN 
 
  - 更新所有行的所有描述
更新
t 
 SET 
 t.Descr = u.Descr 
 FROM 
 tbTotal t 
 INNER JOIN @RowsForUpdate u 
 ON t.EntryDE = u.EntryDE AND t.MetricId = u.MetricId 
 
  - 删除要删除的行
 DELETE t 
 FROM tbTotal t 
 INNER JOIN @RowsForDelete d 
 ON t.EntryDE = u.EntryDE AND t.MetricId = u.MetricId 
 
 END 
  

I have three tables tb1,tb2 and tbTotal. They have the same schemas. The tables have three columns, MetricID, Descr and EntryDE.

What I want is to merge tb1 with tbTotal. I have done this and it works fines. My stored procedure is:

CREATE PROCEDURE [dbo].[Admin_Fill] 
-- Add the parameters for the stored procedure here
@MetricId INT,
@Descr VARCHAR(100),
@EntryDE VARCHAR(20)
 AS

 BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--SET IDENTITY_INSERT dbo.tbTotal ON
-- Insert statements for procedure here
;WITH cte AS (SELECT MetricId=@MetricId,Descr=@Descr,EntryDE=@EntryDE)
 MERGE tbTotal d
 USING cte s
 ON s.EntryDE = d.EntryDE
 AND s.MetricId=d.MetricId
 WHEN matched THEN UPDATE
 set MetricId=s.MetricId,
     Descr=s.Descr,
     EntryDE=s.EntryDE
 WHEN not matched BY TARGET THEN
 INSERT(MetricId,Descr,EntryDE)
 VALUES (s.MetricId,s.Descr,s.EntryDE);
 END

My C# code:

        foreach (DataRow row in dt.Rows) // pass datatable dt1
        {
            MetricId = Convert.ToInt32(row["MetricId"]);
            Descr = row["Descr"].ToString();
            EntryDE = row["EntryDE"].ToString();
            parameters.Add("@MetricId", MetricId);
            parameters.Add("@Descr", Descr);
            parameters.Add("@EntryDE", EntryDE);
            dbaccess.ExecuteNonQuery(strStoredProcedure, parameters); //cmd.ExecuteNonQuery(); 
            parameters.Clear();
        }

Also I want to remove all records in dt2 from dtTotal. I am not sure how to modify the stored procedure.

Thanks for help.

解决方案

If i have understood what you are trying to do correctly, then this is potentially how I would prefer to implement the solution.

I would pass the 2 datatables as TABLE variables to an SP - similar to below and then use JOINs to both UPDATE and DELETE as required using SET operations - thus affecting multiple rows in one query and avoid looping through each row separately.

As mentioned by AdaTheDev in the related answer, you will end up creating a "TABLE" type but there is no drawback to having one extra type and this solution will scale a lot better than a looping approach would.

DISCLAIMER :- Code below may not be syntactically correct but i hope you get the picture of what I am proposing.

CREATE TYPE TableType AS TABLE
(
 MetricId INT, 
 Descr VARCHAR(300) --or whatever length is appropriate, 
 EntryDE INT
);
GO

CREATE PROCEDURE [dbo].[Admin_Fill] 
  @RowsForUpdate TableType READONLY,
  @RowsForDelete TableType READONLY
AS
BEGIN

  -- Update all the Descriptions for all the rows
  UPDATE
    t
  SET
    t.Descr = u.Descr
  FROM
    tbTotal t 
  INNER JOIN @RowsForUpdate u
    ON t.EntryDE = u.EntryDE AND t.MetricId = u.MetricId

  -- Delete the rows to be deleted
  DELETE t 
  FROM tbTotal t
  INNER JOIN @RowsForDelete d
    ON t.EntryDE = u.EntryDE AND t.MetricId = u.MetricId

END

这篇关于合并表（添加，更新和删除不同来源的记录）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！