SQL 如何在 Teradata 中使用单个插入语句插入多条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13971587/
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
How to insert multiple records using single insert statement in Teradata?
提问by PK30
In teradata, can we insert multiple records using single insert statement in query. If yes, how ?
在teradata中，我们可以在查询中使用单个插入语句插入多条记录吗？如果是，如何？
Say I am trying to do something like:
假设我正在尝试执行以下操作：
insert test_rank (storeid,prodid,sales) values (1,'A',1000) ( 2,'B',2000) ,(3,'C',3000);
but this is not working in teradata to insert all 3 records in one statement.
但这在teradata中无法在一个语句中插入所有3条记录。
回答by Anatoly Melentyev
INSERT INTO test_rank (storeid, prodid, sales)
SELECT *
FROM (SELECT *
FROM (SELECT 0 storeid,
1 prodid,
2 sales) T1
UNION ALL
SELECT *
FROM (SELECT 3 storeid,
4 prodid,
5 sales) T2
UNION ALL
SELECT *
FROM (SELECT 6 storeid,
7 prodid,
8 sales) T3
...
)T;
Thanks, Rob! Your advice helped me.
谢谢，罗布！你的建议帮助了我。
回答by Rob Paller
I'm not sure how practical this will be but technically this following is possible:
我不确定这有多实用，但从技术上讲，以下是可能的：
INSERT INTO MyTable
SELECT *
FROM
( SELECT 1 AS StoreID
, 'A' AS ProdID
, 1000 AS SALES
UNION
SELECT 2
, 'B'
, 2000
SELECT 3
, 'C'
, 3000
) DT1
;
Secondly, if you are using BTEQ then you can look into the USING command combined with a flat file repeat single INSERT statement to load the table. But at that point you might as well leverage a proper load utility (MultiLoad or FastLoad) depending on the volumes to accomplish this task if you are doing anything with reasonable volume.
其次，如果您使用的是 BTEQ，那么您可以查看结合平面文件重复单个 INSERT 语句的 USING 命令来加载表。但此时，如果您正在以合理的容量做任何事情，您不妨根据容量利用适当的负载实用程序（MultiLoad 或 FastLoad）来完成此任务。
Edit - 2015-12-10
编辑 - 2015-12-10
The SQL above will not run unless each SELECT in the UNION is first placed in a derived table. See the answer below from Anatoly for the correct syntax.
除非 UNION 中的每个 SELECT 首先放置在派生表中，否则上面的 SQL 不会运行。请参阅下面 Anatoly 的答案以了解正确的语法。
回答by manuel
If you are dealing with small data, you can try putting the values inside a text file and import them with the Teradata SQLA.
如果您正在处理小数据，您可以尝试将值放入文本文件中，然后使用 Teradata SQLA 导入它们。
Create a text file consisting your input, delimited by a tab (use comma if tab doesn't work in your version):
创建一个包含输入的文本文件，以制表符分隔（如果制表符在您的版本中不起作用，请使用逗号）：
1 A 1000
2 B 2000
3 C 3000
Then select the import mode on your SQLA, File -> Import Data, and run this following statement:
然后在 SQLA 上选择导入模式，文件 -> 导入数据，并运行以下语句：
insert into YourTable values (?, ?, ?);
Make sure you create the table beforehand, with the correct data types.
确保事先使用正确的数据类型创建表。
回答by zdeninab
Sometimes is useful to create table with datarather than try to make complicated dynamical insert.
有时用数据创建表而不是尝试进行复杂的动态插入很有用。
CREATE TABLE db.Inc_Config AS (
SELECT
c.calendar_date,
null as Sent_To,
CURRENT_TIMESTAMP as Sent_Date,
date '2016-01-01' as Inc_Start_Date,
date '2016-02-29' as Inc_End_Date
FROM sys_calendar.CALENDAR c
WHERE
c.calendar_date BETWEEN date '2016-01-01' AND date '2016-02-29'
) WITH data;
