sql:删除 + 插入 vs 更新 + 插入 [英] sql: DELETE + INSERT vs UPDATE + INSERT

问题描述

有人问过一个类似的问题，但因为总是视情况而定，所以我单独询问我的具体情况.

A similar question has been asked, but since it always depends, I'm asking for my specific situation separately.

我有一个网站页面，其中显示了一些来自数据库的数据，要从该数据库生成数据，我必须执行一些相当复杂的多连接查询.

I have a web-site page that shows some data that comes from a database, and to generate the data from that database I have to do some fairly complex multiple joins queries.

数据每天(每晚)更新一次.

The data is being updated once a day (nightly).

我想为上述视图预先生成数据以加快页面访问速度.

I would like to pre-generate the data for the said view to speed up the page access.

为此，我正在创建一个包含我需要的确切数据的表.

For that I am creating a table that contains exact data I need.

问题:对于我的情况，先完成表擦除然后插入是否合理?还是我应该更新，插入?

Question: for my situation, is it reasonable to do complete table wipe followed by insert? or should I do update,insert?

SQL 明智的似乎 DELETE + INSERT 会更容易(INSERT 部分是单个 SQL 表达式).

SQL wise seems like DELETE + INSERT will be easier (INSERT part is a single SQL expression).

RDBMS:MS SQL Server 2008 Ent

RDBMS: MS SQL Server 2008 Ent

推荐答案

TRUNCATE 会比 delete 快，所以如果你需要清空表，请改为这样做

TRUNCATE will be faster than delete, so if you need to empty a table do that instead

您没有指定 RDBMS 供应商，但其中一些供应商也有 MERGE/UPSERT 命令 这使您可以在数据存在时更新表，如果数据不存在则插入

You didn't specify your RDBMS vendor but some of them also have MERGE/UPSERT commands This enables you do update the table if the data exists and insert if it doesn't

这篇关于sql:删除 + 插入 vs 更新 + 插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！