删除全部/批量插入 [英] Delete All / Bulk Insert

问题描述

首先让我说我正在SQL Server 2005上运行，所以我无权访问 MERGE 。

First off let me say I am running on SQL Server 2005 so I don't have access to MERGE.

我有一个约有15万行的表，每天都要从文本文件中进行更新。当行脱离文本文件时，我需要从数据库中删除它们，如果它们发生更改或是新的，则需要相应地更新/插入。

I have a table with ~150k rows that I am updating daily from a text file. As rows fall out of the text file I need to delete them from the database and if they change or are new I need to update/insert accordingly.

经过一些测试之后，已经发现，从性能角度来看，完全删除然后从文本文件中批量插入要比执行更新/插入逐行读取文件快得多。但是，我最近遇到了一些讨论如何使用临时表模拟SQL Server 2008的 MERGE 功能和 UPDATE 语句。

After some testing I've found that performance wise it is exponentially faster to do a full delete and then bulk insert from the text file rather than read through the file line by line doing an update/insert. However I recently came across some posts discussing mimicking the MERGE functionality of SQL Server 2008 using a temp table and the output of the UPDATE statement.

我对此很感兴趣，因为我正在研究如何消除表中没有行的Delete / Bulk Insert方法中的时间。我仍然认为这种方法将是最快的，因此我正在寻找解决空表问题的最佳方法。

I was interested in this because I am looking into how I can eliminate the time in my Delete/Bulk Insert method when the table has no rows. I still think that this method will be the fastest so I am looking for the best way to solve the empty table problem.

谢谢

推荐答案

我认为最快的方法是：

1. 将所有外国物品键和索引表中的
   。


2. 截断您的
   表。


3. 批量插入数据。


4. 重新创建外键和
   索引。
li>

1. Drop all foreign keys and indexes from your table.
2. Truncate your table.
3. Bulk insert your data.
4. Recreate your foreign keys and indexes.

这篇关于删除全部/批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！