SQL 从选择插入 (Teradata)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16278791/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert from Select (Teradata)
提问by Kornél Regius
I have the following SQL statement:
我有以下 SQL 语句：
select cast (count(*) as bigint) from
(SELECT oldtable.id,oldtable.day,newtable.newid from oldtable
left outer join newtable on oldtable.day between newtable.FROM_DAY
and newtable.TO_DAY and oldtable.id = newtable.id) a
this results 4.5 billion
这导致 45 亿
but when I say this:
但是当我这样说时：
INSERT INTO AnotherTable
(id, day, newid)
SELECT oldtable.id,oldtable.day,newtable.newid from oldtable
left outer join newtable on oldtable.day between newtable.FROM_DAY
and newtable.TO_DAY and oldtable.id = newtable.id
it only inserts 300 million records (oldtable contains 4.5 billion records, newtable 430 million).
它只插入 3 亿条记录（oldtable 包含 45 亿条记录，newtable 包含 4.3 亿条记录）。
Why?
为什么？
The definiton of AnotherTable:
另一个表的定义：
CREATE MULTISET TABLE AnotherTable ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id INTEGER NOT NULL,
day DATE FORMAT 'YYYY-MM-DD',
newid INTEGER NOT NULL
)
PRIMARY INDEX ( id)
PARTITION BY RANGE_N(day BETWEEN DATE '2000-09-20' AND DATE '2030-02-15' EACH INTERVAL '1' DAY );
I made the following checking:
我做了以下检查：
SELECT oldtable.id,oldtable.day,newtable.newid from oldtable
left outer join newtable on oldtable.day between newtable.FROM_DAY and newtable.TO_DAY
and oldtable.id = newtable.id
where newtable.newid is null
It resulted 0 records, so outer join is not needed at all, I just use it here to demonstrate that the record number is different, but it shouldn't be
结果是0条记录，所以根本不需要外连接，我这里只是用它来证明记录号不同，但不应该
采纳答案by dnoeth
Where did you get the number of inserts from?
你从哪里得到插入的数量？
4.5 billion minus 300 million is approx. 4.2 billion.
45亿减去3亿大约是。42亿。
When you exactly calculate the number of missing rows is the result 4.294.967.296?
当您准确计算缺失行数时，结果是 4.294.967.296？
Then it might be due to "Warning: 6813 Numeric overflow in internal counters."
那么它可能是由于“警告：内部计数器中的 6813 数字溢出”。
Cut&Paste from the Messages manual:
消息手册中的剪切和粘贴：
6813 Numeric overflow in internal counters. The number of rows returned is actual number of rows returned, modulo 2^32. Explanation: This warning message is sent within SUCCESS/OK parcel when the activitycount (i.e., the number of rows) returned, overflows the current max limit of (2^32 - 1). The request is successful. The value returned to the user is : (actual number of rows returned) modulo 2^32.
6813 内部计数器中的数字溢出。返回的行数是实际返回的行数，取模 2^32。说明：当返回的活动计数（即行数）超出当前最大限制 (2^32 - 1) 时，在 SUCCESS/OK 包内发送此警告消息。请求成功。返回给用户的值是：（返回的实际行数）模 2^32。
NOTE: If the user knows that the actual number of rows returned cannot exceed 2^33, then the actual number of rows returned can be derived as follows: actual number of rows returned = 2^32 + the value returned to the user.
注意：如果用户知道实际返回的行数不能超过2^33，那么实际返回的行数可以推导出来：实际返回的行数=2^32+返回给用户的值。
Dieter
迪特
回答by SaiMandagondi
You should create a multi set table. It will do the trick!
您应该创建一个多组表。它会做的伎俩！
CREATE MUTLISET TABLE ();
INSERT INTO ()
SELECT ()
The above syntax worked for me!
上面的语法对我有用！
