Insert Into Statement in Teradata
Contents
Insert into statement is used to insert a record into a table in Teradata. The syntax of the insert query in Teradata is same like other relational databases such as Oracle, Mysql, SqlServer and so on.
Syntax of Insert statement
|
1 2 3 4 |
INSERT INTO <Database_Name>.<Table_Name> (Column1,Column2,..ColumnN) VALUES (Value1,Value2,...ValueN); |
- The database name and table name should be mentioned after the Insert into command.
- Here the Column1,Column2,..ColumnN are denotes the name of the each column. If you are adding the values for all the columns, column names are optional in the Insert Statement.
- The Value1,Value2,..ValueN are mentions the value that you want to insert into the table.
Example of Insert Into statement In Teradata
Lets create the Patient table in the Hospital Database and add the few records into it using Insert Into statement.
|
1 2 3 4 5 6 7 8 9 |
CREATE SET TABLE Hospital_DB.Patient,FALLBACK ( Patient_Id INTEGER, Patient_Name VARCHAR(30), Age NUMBER, Gender VARCHAR(30), BloodGroup VARCHAR(30), VisitDate DATE FORMAT 'YYYY-MM-DD' ) UNIQUE PRIMARY INDEX ( Patient_Id ); |
The above create statement has executed in Teradata and it created the Patient table in Hospital_DB.
Now we can run the insert query to add the records into the patient table.
|
1 2 3 |
INSERT INTO Hospital_DB.Patient VALUES (151,'Alex',45,'Male','B Positive','2020-01-12'); |
Common Errors in Insert Statement of Teradata
Error 1 :
Lets try to add the multiple records using Insert statement in Teradata
|
1 2 3 4 5 |
INSERT INTO pp_scratch.Patient VALUES (187,'Jenifer',37,'Female','O Negative','2019-12-05'), (192,'Stephen',54,'Male','A Positive','2020-01-19'), (195,'Mike',48,'Male','B Negative','2020-01-05'); |
Since Teradata won’t support Values with Multiple rows in Insert statement, It will throw the following error message.
|
1 |
INSERT Failed : 3706: "Syntax error: expected something between ')' and ','." |
To resolve this issue, Teradata provides INSERT INTO SELECT statement to add the multiple values. First we can insert all the records into one temporary table and then we can execute the INSERT INTO SELECT statement to add the multiple records.
Error 2 : The positional assignment has too few values
The number of columns in the Patient table is 6. Lets try to insert the 5 values into this table like below.
|
1 2 3 |
INSERT INTO Hospital_DB.Patient VALUES (187,'Jenifer',37,'Female','2019-12-05'); |
In the above Insert query, we didn’t include the value for the BloodGroup column(5th column in the Create table). Since the number of columns is not matched with the number of values in the Insert statement,Teradata has thrown the following error message
|
1 |
INSERT Failed. 3812: The positional assignment list has too few values. |
To resolve this issue, we need to specify the column names along with values in the Insert statement.
|
1 2 3 4 |
INSERT INTO Hospital_DB.Patient (Patient_Id,Patient_Name,Age,Gender,VisitDate) VALUES (187,'Jenifer',37,'Female','2019-12-05'); |
Since we didn’t add the value for BloodGroup column in the Insert statement,the NULL value has added into it.
Recommended Articles
