方式做批量更新和插入 [英] Way to do a batch update and insert

问题描述

我在寻找一些技巧和事情要考虑这个问题。
我从Web服务调用得到上千条记录。
有关数据项ID，的startDate，termDate，名称，以及其他一些领域。
有填充了一些记录的分贝。
唯一性是ID +的startDate + termDate，请注意，可能有很多相同的ID，具有不同的startDate和/或termDate。

I'm looking for some tips and things to consider for this issue. I'm getting thousands of records from a web services call. Relevant data items are id, startDate, termDate, name, and some other fields. There is a db populated with some records. Uniqueness is id + startDate + termDate, note that there may be many same ids, with differing startDate and/or termDate.

从Web服务调用的记录，我需要找到匹配的唯一约束的记录，并为这些记录，做一个更新。
从Web服务，这是不是在数据库的记录（ID +的startDate + termDate），我需要插入的记录。

For the records from the web services call, I need to find the records that match on the unique constraint, and for those records, do an update. For records from the web services that are NOT in the db (id + startDate + termDate), i need to insert the record.

应用程序当前使用iBatis的。
我需要一个好办法某个方向做'不记录存在'存在常规，以及如何跟踪需要用于更新与插入Web服务的记录。

The application currently uses iBatis. I need some direction on a good way to do the 'does the record exist' exist routine, and how to keep track of the web services records that need to be used for an update vs. an insert.

那么，一旦我有这两套reocrds的，我想知道什么是应该做的工作分贝最好的方式。
它是最好的一次遍历一次记录和做的工作（插入或更新），或者是一个更好的主意，做​​一个大的更新或插入了所有在其记录的字符串，因此，实际上，我会有两个调用数据库，一个用于更新，一个用于插入（但SQL将是巨大的）？

Then, once I have these two sets of reocrds, I'm wondering what is best way to do the db work. Is it best to iterate over once record at a time and do the work (insert or update), or is it a better idea to make one big update or insert string that has all of the records in it, so in effect, i would have two db calls, one for update and one for insert (but the sql would be huge)?

推荐答案

保存从Web服务记录到一个文本文件。然后做一个批量插入到一个空的临时表。在code将根据RDBMS而有所不同。

Save the records from the web service in a text file. Then do a batch insert into an empty temporary table. The code will vary according to the rdbms.

一旦你的临时表做插入：

Once you have the temporary table do the insert:

insert into my_table (id, startDate, termDate, name)
select t.id, t.startDate, t.termDate, t.name
from temp_table t
left outer join my_table my on 
    t.id = my.id
    and
    t.startDate = my.startDate
    and
    t.termDate = my.termDate
where my.id is null

和更新：

update my_table
set 
    id = t.id, 
    startDate = t.startDate, 
    termDate = t.termDate, 
    name = t.name
from temp_table t
inner join my_table my on 
    t.id = my.id
    and
    t.startDate = my.startDate
    and
    t.termDate = my.termDate
where 
    id = t.id
    and
    startDate = t.startDate
    and 
    termDate = t.termDate

在没有测试更新的语法上面应该PostgreSQL的工作。如果指定RDBMS我可以改变它。

The not tested update syntax above should work in postgresql. If you specify the rdbms I could change it.

这篇关于方式做批量更新和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！