在 SQL Server 2008 中插入/更新大量数据的最佳实践 [英] Best practices for inserting/updating large amount of data in SQL Server 2008

问题描述

我正在构建一个系统，用于通过各种 CSV 提要更新大量数据.通常我会遍历提要中的每一行，执行选择查询以检查项目是否已存在，并根据项目是否存在插入/更新项目.

I'm building a system for updating large amounts of data through various CSV feeds. Normally I would just loop though each row in the feed, do a select query to check if the item already exists and insert/update an item depending if it exists or not.

我觉得这种方法的可扩展性不是很高，并且可能会在较大的提要上影响服务器.我的解决方案是像往常一样循环遍历这些项目，但将它们存储在内存中.然后，对于每 100 个左右的项目，对这 100 个项目进行选择，并获取数据库中匹配的现有项目的列表.然后将插入/更新语句连接在一起并将它们运行到数据库中.这基本上会减少访问数据库的次数.

I feel this method isn't very scalable and could hammer the server on larger feeds. My solution is to loop through the items as normal but store them in memory. Then for every 100 or so items do a select on those 100 items and get a list of existing items in the database that match. Then concatenate the insert/update statements together and run them into the database. This would essentially cut down on the trips to the database.

这是一个足够可扩展的解决方案吗?是否有任何关于将大型 Feed 导入生产环境的示例教程?

Is this a scalable enough solution and are there any example tutorials on importing large feeds into a productive environment?

谢谢

推荐答案

看到你使用的是SQL Server 2008，我推荐这种方法:

Seeing that you're using SQL Server 2008, I would recommend this approach:

• 首先将 CSV 文件批量复制到临时表中
• 使用 MERGE 命令从该临时表更新您的目标表

查看 MSDN 文档 和 伟大的博客发布 如何使用 MERGE 命令.

Check out the MSDN docs and a great blog post on how to use the MERGE command.

基本上，您在实际数据表和暂存表之间创建一个基于通用标准(例如通用主键)的链接，然后您可以定义何时该做什么

Basically, you create a link between your actual data table and the staging table on a common criteria (e.g. a common primary key), and then you can define what to do when

• 行匹配，例如该行同时存在于源表和目标表中 --> 通常你要么更新一些字段，要么一起忽略它
• 源中的行在目标中不存在 --> 通常是 INSERT 的情况

你会有一个类似这样的 MERGE 语句:

You would have a MERGE statement something like this:

MERGE TargetTable AS t
USING SourceTable AS src
ON t.PrimaryKey = src.PrimaryKey

WHEN NOT MATCHED THEN
  INSERT (list OF fields)
  VALUES (list OF values)

WHEN MATCHED THEN
  UPDATE
    SET (list OF SET statements)
;

当然，如果需要，ON 子句可能会涉及更多.当然，您的 WHEN 语句也可以更复杂，例如

Of course, the ON clause can be much more involved if needed. And of course, your WHEN statements can also be more complex, e.g.

WHEN MATCHED AND (some other condition) THEN ......

等等.

MERGE 是 SQL Server 2008 中一个非常强大且非常有用的新命令 - 如果可以，请使用它！

MERGE is a very powerful and very useful new command in SQL Server 2008 - use it, if you can!

这篇关于在 SQL Server 2008 中插入/更新大量数据的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！