主页 / dba / 问题 / 31460
Accepted
Dina
Asked: 2013-01-05 11:52:20 +0800 CST

将行从一个表移动到另一个表

  • 772

作为归档过程的一部分，我正在将记录从一个数据库移动到另一个数据库。我想将行复制到目标表，然后从源表中删除相同的行。

我的问题是，在删除行之前检查第一次插入是否成功的最有效方法是什么。

我的想法是这样的，但我觉得有更好的方法：

@num_records=select count(ID) from Source_Table where (criteria for eligible rows)

insert * into Destination_Table where (criteria for eligible rows)

if ((select count(ID) from Destination_Table where (criteria) )=@numrecords)

delete * from Source_Table where (criteria)

将它与 RAISERROR 功能结合起来会更好/可能吗？谢谢！

sql-server-2012

4 个回答

  1. 如果您的存档表没有。

    • 已启用在其上定义的触发器。
    • 参与 FOREIGN KEY 约束的任一侧。
    • 有 CHECK 约束或启用规则。

    你也可以在一个语句中做到这一点。

    DELETE FROM source_table
OUTPUT deleted.Foo,
       deleted.Bar,
       SYSUTCDATETIME()
INTO archive_table(Foo, Bar, archived)
WHERE  Foo = 1;

    这将作为一个单元成功或失败，并且还避免了可能的竞争条件，其中将行添加INSERT到存档和之间DELETE（尽管您的WHERE子句很可能使这种情况极不可能发生）。

    • 19
  2. Best Answer

    我会推荐TRY/CATCH语法以及显式事务。我对此解决方案的假设是插入失败的原因是某种可捕获的 SQL 错误（例如密钥违规、数据类型不匹配/转换错误等）。结构如下所示：

    BEGIN TRAN

BEGIN TRY
  INSERT INTO foo(col_a,col_b,col_c,recdate)
  SELECT col_a,col_b,col_c,recdate
  FROM bar
  WHERE recdate BETWEEN @startdate AND @enddate

  DELETE FROM bar
  WHERE recdate BETWEEN @startdate AND @enddate

  COMMIT TRAN
END TRY
BEGIN CATCH
  ROLLBACK TRAN
END CATCH

    这种结构的工作方式是，如果在 INSERT 或 DELETE 中发生任何错误，则整个操作都会回滚。这保证了整个动作必须成功才能完成。如果您认为有必要，您可以将其与2012 年的THROW或2008 年及之前的RAISERROR结合起来，以添加额外的逻辑并在未满足该逻辑时强制回滚。

    另一种选择是查看SET XACT_ABORT ON，尽管我觉得 TRY/CATCH 语法为您提供了更多的粒度。

    • 13

  3. 我考虑归档的方式（我确信这也不完美）是在新的归档表中添加一个位列，如“已归档”，在成功传输记录后其值为 1。一旦您传输了所有记录，您就可以执行删除操作，同时还可以从存档表中查找此“存档”字段值“1”，即 True。

    我同意 Mike 关于使用 Try/Catch 的观点。

    • 1

  4. 尝试这个：

    INSERT dbo.newtable(
      name,
      department,
      Salary
) SELECT 
            name,
            FirstName,
            Lastname
      FROM    (
           DELETE dbo.oldtable
           OUTPUT
                   DELETED.name,
                   DELETED.department,
                   DELETED.Salary
           WHERE ID  IN ( 1001, 1003, 1005 )
      ) AS RowsToMove;

SELECT * FROM dbo.newtable;
SELECT * FROM dbo.oldtable;
    • 1

相关问题