EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Data Warehouse Tutorial Insert into Teradata
 

Insert into Teradata

Priya Pedamkar
Article byPriya Pedamkar

Updated April 20, 2023

Insert into Teradata

 

 

Intorduction to Insert into Teradata

The Insert into statement is utilized to accommodate a new row or record into the table. During the execution of this statement, No records are returned to the user except the status mentioning something like ‘x’ records have been inserted successfully. In this topic, we are going to learn about Insert into Teradata. It accounts for all the values in the table, may it be a NULL or some data value.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

An insert statement consists of a list of columns and values to be inserted into those columns. If we are specifying the list of values as per the data structure of the table then the list of columns can be skipped and directly the values to be inserted can be specified.

What is Insert Into in Teradata?

Let’s have a look at the syntax that insert into follows with Teradata:

INSERT INTO table_name (col1, col2, col3, col4, col5, col6, col7)
VALUES ( 1, 'EDU', 'CBA', 5, 5, 5.5, 7);

The above syntax is as per the ANSI syntax that works quite well with Teradata.

  • It consists of two keywords, INSERT INTO & VALUES
  • table_nameis the name of the table in which the row needs to be inserted
  • table_name is followed by the list of columns to which the values need to be inserted
  • The keyword VALUES is followed by the actual values that need to be inserted into the table

We can skip mentioning the column names specifically as well if we are mentioning the column values in the specific order as per the order of columns in the table structure.

Let’s take into consideration the below table with 6 columns having the mentioned data types, along with attributes defined.

Insert into Teradata table

The alternative where column names can be skipped is as below:

INSERT INTO table_name VALUES( 'edu' , 3.2 , 4 , , NULL , '21-01-2020' )

Once we execute the above Insert Into statement, there will be a new row in the table with

  • column1 having the value as edu
  • column2 having the value as 3.2
  • column 3 having the value as 4
  • and so on until column 6 having the date value as 21-01-2020

Whereas Column 4 is having a missing value out there in the Insert into the statement, so column 4 will also have the missing data

And column5 will have Null as specified in the Insert into statement above.

Let’s discuss the same taking up the trivial syntax:

INSERT INTO table_name (column1 , column2 , column6 , column3 )
VALUES ( 'edu' , 3.2 , '21-01-2020' , 4)

Here we have specified the column name explicitly along with the corresponding values for each column

  • Column1 will have the value edu
  • column2 will have the value 3.2
  • Column6 is the next one in the list that’s why it will have the value of 21-01-2020 as date
  • Whereas column3 will have the value as 4

Now as we have not even specified in this insert into the statement, column4, and column5.

What will column4 and column5 be holding?

Yes, The missing columns will be assumed as Null and will hold Null values after this statement is executed.

This is generally a good format when the data in the files are not coming in the specified order and does not matches the order of the Table columns

How to Insert in Teradata?

There are multiple ways in which we can have the Insert statement written for different requirements. Let’s Discuss some of them down below:

Case #1

We want to insert multiple records using the Insert Statement

Let’s take an example to understand the same:

INSERT INTO table_name
VALUES( 'edu' , 3.2 , 4 , , NULL , '21-01-2020' ),
( 'CBA' , 4.2 , 5 , , NULL , '22-01-2020' ),
( 'Learn' , 5.2 , 7 , , NULL , '24-01-2020' ),
( 'Online' , 6.2 , 8 , , NULL , '25-01-2020' ),
( 'data' , 8.2 , 9 , , NULL , '27-01-2020' ),
( 'science' , 9.2 , 99 , , NULL , '28-01-2020' ),
( 'Teradata' , 5.2 , 7 , , NULL , '24-01-2020' ),
( 'Python' , 6.2 , 8 , , NULL , '25-01-2020' ),
( 'Tableau' , 8.2 , 9 , , NULL , '27-01-2020' ),
( 'stats' , 9.2 , 99 , , NULL , '28-01-2020' )

The above statement will interest 10 rows to the table having the above-mentioned values.

When we are trying to insert multiple rows using a single insert statement, then each new row in the Insert into statement is separated by a comma.

If in case this comma is missed, Teradata will throw an exception or specifically a syntax error.

Case #2

We want to insert multiple records using the Insert Statement, but the records are coming from a separate table

Let’s take an example to understand the same:

INSERT INTO table_name
SELECT * FROM other_table

The syntax for Insert part is similar to what we have seen so far, but the values section is replaced by the ‘SELECT’ statement

Here this Select statement will be providing the values indirectly and the same will be inserted into the table.

  • The select statement selection the values from the other_table should have the same order of columns as expected by the table structure of the table the values are getting inserted into
  • Moreover, the data type needs to be the same as well
  • If in case the column order isn’t the same then we can either specifically mention the column names in the insert statement like below:
  • INSERT INTO table_name (column1 , column2 , column6 , column3 ) SELECT * FROM other_table
  • Or can select specific columns from the select statement itself like below:
  • INSERT INTO table_name SELECT column1 , column2 , column6 , column3 FROM other_table

This will serve as a workaround in such scenarios.

Conclusion

  • INSERT INTO statement is used to insert new records into the Teradata tables
  • We can specifically mention the column names if the values to be entered are in a different order as compared to that of the column order of the table
  • We can enter multiple records at the same time as well
  • A separate table or a file can also be used as the source for these values to be inserted

Recommended Articles

We hope that this EDUCBA information on “Insert into Teradata” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Teradata Architecture
  2. Teradata Partition by
  3. Primary Index in Teradata
  4. What is Teradata?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW