插入多行 SQL Teradata
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39668309/
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 Multiple Rows SQL Teradata
提问by Bocean
I am creating a volatile table and trying to insert rows to the table. I can upload one row like below...
我正在创建一个易失性表并尝试向表中插入行。我可以像下面这样上传一行...
create volatile table Example
(
ProductID VARCHAR(15),
Price DECIMAL (15,2)
)
on commit preserve rows;
et;
INSERT INTO Example
Values
('Steve',4);
However, when I try to upload multiple I get the error:
但是，当我尝试上传多个时，出现错误：
"Syntax error: expected something between ')' and ','."
“语法错误：预期在 ')' 和 ',' 之间。”
INSERT INTO Example
Values
('Steve',4),
('James',8);
回答by dnoeth
As Gordon said, Teradata doesn't support VALUES with multiple rows (and the UNION ALL will fail because of the missing FROM.
正如戈登所说，Teradata 不支持多行的 VALUES（由于缺少 FROM，UNION ALL 将失败。
You can utilize a Multi Statement Request (MSR) instead:
您可以改用多语句请求 (MSR)：
INSERT INTO Example Values('Steve',4)
;INSERT INTO Example Values('James',8)
;
If it's a BTEQ job the Inserts are submitted as one block after the final semicolon (when there's a new command starting on the same line it's part of the MSR). In SQL Assistant or Studio you must submit it using F9instead of F5.
如果它是 BTEQ 作业，则插入作为最后一个分号之后的一个块提交（当在同一行上有一个新命令时，它是 MSR 的一部分）。在 SQL Assistant 或 Studio 中，您必须使用F9而不是F5.
回答by Gordon Linoff
I don't think Teradata supports the multiple row valuessyntax. Just use select:
我认为 Teradata 不支持多行values语法。只需使用select：
WITH dual as (SELECT 1 as x)
INSERT INTO Example(ProductId, Price)
SELECT 'Steve' as ProductId, 4 as Price FROM dual UNION ALL
SELECT 'James' as ProductId, 8 as Price FROM dual;
回答by access_granted
create table dummy as (select '1' col1) with data;
INSERT INTO Student
(Name, Maths, Science, English)
SELECT 'Tilak', 90, 40, 60 from dummy union
SELECT 'Raj', 30, 20, 10 from dummy
;
回答by Swastik Raj Ghosh
I found a solution for this via RECURSIVE. It goes like this:-
我通过 RECURSIVE 找到了解决方案。它是这样的：-
INSERT INTO table (col1, col2)
with recursive table (col1, col2) as
(select 'val1','val2' from table) -- 1
select 'val1','val2' from table -- 2
union all select 'val3','val4' from table
union all select 'val5','val6' from table;
Data of line 1 does not get inserted (but you need this line). Starting from line 2, the data you enter for val1, val2 etc. gets inserted into the respective columns. Use as many UNION ALLs' as many rows you want to insert. Hope this helps :)
第 1 行的数据未插入（但您需要此行）。从第 2 行开始，您为 val1、val2 等输入的数据被插入到相应的列中。使用尽可能多的 UNION ALL 和您想要插入的行数。希望这可以帮助 ：）
回答by Eman4real
At least in our version of Teradata, we are not able to use an insert statement with a CTE. Instead, find a real table (preferably small in size) and do a top 1.
至少在我们的 Teradata 版本中，我们不能使用带有 CTE 的插入语句。相反，找一张真正的桌子（最好是小尺寸的）并做一个前 1。
Insert Into OtherRealTable(x, y)
Select top 1
'x' as x,
'y' as y
FROM RealTable
回答by access_granted
CTE syntax (working):
CTE 语法（工作）：
insert into target_table1 (col1, col2)
with cte as (select 1 col1)
select 'value1', 'value2' from cte
union all
select 'value1a', 'value2a' from cte
;
CTE Syntax not working in Teradata (error: expected something between ")" and the "insert" keyword)
CTE 语法在 Teradata 中不起作用（错误：预期介于“)”和“插入”关键字之间
with cte as (select 1 col1)
insert into target_table1 (col1, col2)
select 'value1', 'value2' from cte
union all
select 'value1a', 'value2a' from cte
;
