Insert Multiple Rows SQL Teradata

Question

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);

Answer 1

As Gordon said, Teradata doesn't support VALUES with multiple rows (and the UNION ALL will fail because of the missing FROM.

You can utilize a Multi Statement Request (MSR) instead:

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 F9 instead of F5.

Answer 2

I don't think Teradata supports the multiple row values syntax. Just use select:

INSERT INTO Example(ProductId, Price)
WITH dual as (SELECT 1 as x)
    SELECT 'Steve' as ProductId, 4 as Price FROM dual UNION ALL
    SELECT 'James' as ProductId, 8 as Price FROM dual;

Answer 3

CTE syntax (working):

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)

with cte as (select 1 col1)
insert into target_table1 (col1, col2)
select 'value1', 'value2' from cte
union all
select 'value1a', 'value2a' from cte
;

Answer 4

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
;

Answer 5

I found a solution for this via RECURSIVE. It goes like this:-

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 :)

Answer 6

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.

Insert Into OtherRealTable(x, y)
Select top 1
   'x' as x, 
   'y' as y
FROM RealTable

Answer 7

yes you can try this.

INSERT INTO  Student 
  SELECT (Name, Maths, Science, English)  FROM JSON_Table 
        (ON (SELECT 1 id,cast('{"DataSet" : [
{"s":"m", "Name":"Tilak", "Maths":"90","Science":"40", "English":"60" },   
{"s":"m", "Name":"Raj", "Maths":"30","Science":"20", "English":"10" }
]
}' AS json ) jsonCol)
USING rowexpr('$.DataSet[*]')
colexpr('[{"jsonpath":"$.s","type":"CHAR(1)"},{"jsonpath":"$.Name","type":"VARCHAR(30)"}, {"jsonpath":"$.Maths","type":"INTEGER"}, {"jsonpath":"$.Science","type":"INTEGER"}, {"jsonpath":"$.English","type":"INTEGER"}]')
) AS JT(id,State,Name, Maths, Science, English)