每次只选择一组特定的行 [英] SELECT only a certain set of rows at a time

问题描述

我需要从一个表中选择数据并将其插入到另一个表中。目前SQL看起来像这样：

I need to select data from one table and insert it into another table. Currently the SQL looks something like this:

   INSERT INTO A (x, y, z)
   SELECT x, y, z
   FROM B b
   WHERE ...

是巨大的，导致超过200万行，我们认为它占用了太多的内存。

However, the SELECT is huge, resulting in over 2 millions rows and we think it is taking up too much memory. Informix, the db in this case, runs out of virtual memory when the query is run.

我如何去选择和插入一组行（比如说2000）。在这种情况下， ？假设我不认为有任何行ids等。

How would I go about selecting and inserting a set of rows (say 2000)? Given that I don't think there are any row ids etc.

推荐答案

你可以从表中选择SELECT FIRST n *。其中n是您想要的行数，例如2000.此外，在WHERE子句中，嵌入式选择将检查您插入的表中已经存在的行。因此，下次运行语句时，不会包含已插入的数据。

You can do SELECT FIRST n * from Table. Where n is the amount of rows you want, say 2000. Also, in the WHERE clause do an embedded select that checks the table you are inserting in to for rows already existing. So that the next time the statement is ran, it will not include already inserted data.

这篇关于每次只选择一组特定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！