How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

UDA

How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

I am new to Teradata. I have a clear understanding about using the SELECT statement.

I would like to create a make table something like the one you are able to use in access. But anytime I try to use the select and INTO it say I do not have permission.
I am guessing that there is a personal database within Teradata where I can create this table or temporary tables without it being created within the data warehouse.

I need some direction or a link where I can learn how to do this. I think within the Select INTO statement I have to include the part of my personal database. But I do not know how.
Here is an example of what I would like to do. There is already a table1 and table2 within the company data warehouse and I will like to insert the result into my personal database call JOHN. Basically I will be Selecting data from the company’s data warehouse (This database does not allow you to create) and into a personal database I created within the Teradata explorer.

SELECT T1.* INTO jump
FROM Table1 AS T1 INNER JOIN table2 ON T1.ID=table2.ID
WHERE (((T1.amt)<=(SELECT Max(Amt) FROM Table1 As T2 WHERE T2.ID = T1.ID AND (SELECT COUNT(*) FROM Table1 AS T3 WHERE T3.ID = T2.ID AND T3.Amt <= T2.Amt) <= table2.Audit)));

Any help will be greatly appreciated. Please a step by step will be great.
7 REPLIES

Re: How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

You can go through the data definition and sql manuals from teradata to learn detailed syntax,

but here are some samples

/* Table JOHN.Jump already exists and the column data types match those of the select list, you should find this in sql manual */

INSERT INTO JOHN.JUMP
SELECT col1, col2
FROM t1, t2
where t1.key = t2.key
;

/* create a new table using the o/p of a select statement, this should be in data definition manual */
CREATE TABLE JOHN.JUMP
AS
(
SELECT col1, col2
FROM t1, t2
where t1.key = t2.key
)
WITH DATA
/* Primary index definition is option but is extremely critical to chose the right one for data distribution and performance */
PRIMARY INDEX(col1)
;

Re: How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

Thanks for your prompt reply. But i am getting
Incorrect syntax near the Keyword ‘AS’. This is when I attempted to create the table.

So I cannot simply use the SELECT INTO? Currently I already have table1 and table1. Not possible to make table?

Re: How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

The create table AS sample code should work.

Double check yoursql

Re: How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

I am still having trouble. Please take a look at the print screen i have uploaded and tell me what i am doing wrong. I am sure it is user error on my part.[url=http://itismystory.com/upload/Help.doc][/url]

http://itismystory.com/upload/Help.doc

Thanks a million
N/A

Re: How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

Your screenshot shows an ODBC connection to an MS SQL Server, which doesn't support CREATE TABLE AS.

But the SELECT INTO syntax is used in MS SQL to materialize a new table, this should work if the target table doesn't exists yet.

Dieter

Re: How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

Thanks.
SO then

SELECT T1.* INTO JOHN.JUMP
FROM Table1 AS T1 INNER JOIN table2 ON T1.ID=table2.ID
WHERE (((T1.amt)<=(SELECT Max(Amt) FROM Table1 As T2 WHERE T2.ID = T1.ID AND (SELECT COUNT(*) FROM Table1 AS T3 WHERE T3.ID = T2.ID AND T3.Amt <= T2.Amt) <= table2.Audit)));

This should create a make table into database JOHN which is one of the database on my left. But i am getting an error "either does not exist or you do not have permission to use it".

What am i doing wrong

It works great if I use

SELECT T1.* INTO JUMP
FROM Table1 AS T1 INNER JOIN table2 ON T1.ID=table2.ID
WHERE (((T1.amt)<=(SELECT Max(Amt) FROM Table1 As T2 WHERE T2.ID = T1.ID AND (SELECT COUNT(*) FROM Table1 AS T3 WHERE T3.ID = T2.ID AND T3.Amt <= T2.Amt) <= table2.Audit)));

But it does not let my create the table in a specific table. I would like to understand this approach so i can create tables not in my company's data warehouse server but a personal database

Re: How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE

I agree with dnoeth - you use MS SQL server. Example in your last post works in your database, where default schema dbo is used, but when you try to do it in John db, the syntax should follow full identification: db_name.schema_name.table_name, for instance John.dbo.Jump, and you have to have proper permissions. For more info look at MS Books Online (= SQL server help).

Still if the corporate DW is on Teradata, you'd need either some (ETL) tool (like Integration service - former DTS) to move data between environments or link Teradata to SQL server. All these stuffs are inflexible and slow. I'd encourage you to use directly Teradata. The database object "user" (i.e. your db account) can have own permanent space at your disposal or you can share some db with colleagues.

petr