EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Data Warehouse Tutorial Insert into Teradata
Secondary Sidebar
Teradata Tutorial
  • Basic
    • What is Teradata
    • Career In Teradata
    • Teradata Architecture
    • Teradata data types
    • Teradata ODBC Driver
    • Teradata Vantage
    • Insert into Teradata
    • Teradata CASE Statement
    • Teradata Partition by
    • Teradata Date Formats
    • Teradata Current Date
    • Teradata Substring
    • Teradata BTEQ
    • Teradata Concatenate
    • Teradata REPLACE
    • Teradata Joins
    • Primary Index in Teradata
    • Fastload in Teradata
    • Collect Stats in Teradata
    • Teradata Volatile Table
    • Teradata TPT
    • Teradata Qualify
    • Teradata?coalesce
    • Teradata RENAME TABLE
    • Teradata Viewpoint
    • Teradata Performance Tuning
    • Teradata AMP
    • Teradata Utilities

Insert into Teradata

By Priya PedamkarPriya Pedamkar

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.

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What is Insert Into in Teradata?

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

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,060 ratings)

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

This is a guide to Insert into Teradata. Here we discuss what is Insert into Teradata and multiple ways in which we can have the Insert statement written for different requirements. You may also look at the following articles to learn more –

  1. Teradata Architecture
  2. Teradata Partition by
  3. Primary Index in Teradata
  4. What is Teradata?
Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)
  360+ Online Courses |  1500+ Hours |  Verifiable Certificates |  Lifetime Access
4.7
Price

View Course

Related Courses

Business Intelligence Training (12 Courses, 6+ Projects)4.9
Data Visualization Training (15 Courses, 5+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more