I am totally new to Teradata. I need help with SQL table syntax for creating, inserting tables and view.
I do have one requirement.
1) Create one table called : Employee contains Emp_ID, First_Name, Last_Name etc..
2) Create second table called: Sales - Emp_ID, Sales_amout, Date etc.
Primary Key: EMP_ID on employee table
Foreign key: EMP_ID on Sales table reference Employee table.
I need to create view based on above two tables. I also want to insert data directly from view.
Can anyone please?
Thanks all for your ehlp
Thanks Dnoeth for your reply.
Is there any way, if i insert/update records on sales table will automitically update Employee table as well?
Of course there are Triggers, but they're hardly used in a DWH, this is usually done as a part of the loading process.
What exactly do you want to achieve?
"populated into Employee table" = aggregation?
Sales tables tend to be large with billions of rows, maintaining denormalized data will be a huge overhead. An Aggregate Join Index might help, then there's no need to update the employee table.
CREATE TABLE EMPLOYEE (
Primary Key (EMP_ID) );
CREATE TABLE Sales (
ALTER TABLE Sales
ADD FOREIGN KEY (EMP_ID) REFERENCES Employee (EMP_ID);
MERGE INTO EMPLOYEE
USING SALES S
ON EMPLOYEE.EMP_ID = S.EMP_ID
WHEN NOT MATCHED THEN
INSERT (EMP_ID,EMP_NAME) VALUES (S.EMP_ID, S.EMP_NAME);
Does this work?
This will not work, as a row must be inserted into Employee before it can be inserted into Sales.
It's a wrong approach, this should be done during ETL.