Create DB & Import Excel data

Database
Highlighted
Enthusiast

Create DB & Import Excel data

Hi,

In my attempts to connect a datasource to a new database in SQL Assistant (SQLA) using VM Ware, I'm stuck at the import new data step.

Last post is at this link;

https://community.teradata.com/t5/Database/Unable-to-Create-Tables-with-SQL-Assistant/m-p/83678#M339...

 

I have connected to SQLA and I want to import an Excel dataset to query some data.

So I have the connection ready & went to import my data.

I first tried to create a table but got the error that I don't have permission.

teradata_create_tbl_error.gif

See above  error for theb below code;

CREATE MULTISET TABLE WKLY_SALES
(
ID SMALLINT NOT NULL,
WeekCommencing CHAR(10),	
State VARCHAR(5),	
OrderCount INT
)
PRIMARY INDEX(ID);

So then I tried to run the create database code which ran but the create table code still doesn't help as the same error about the DBC database applies.

 

Can someone advise how I can import some data into either DBC or a DB I create so I can query the data.

Thanks


Accepted Solutions
Senior Apprentice

Re: Create DB & Import Excel data

Hi Peter,

 

You're permissions error relating to database DBC is because you are trying to create your table in database DBC.

 

When you logon to a TD system you are doing exactly that, logging in to the system. With some dbms's you login to a database.

 

On a TD system there will be many databases. When you issue any SQL command, if an object (e.g. tables) is not qualified by a database name the system will assume they are in your default database (temp tables may be different but we'll ignore those for now).

 

In your scenario you either change your CREATE TABLE statement to specify that table WKLY_SALES is in database A_PETER_DB :

  CREATE MULTISET TABLE A_PETER_DB.WKLY_SALES...

or you set your default database using:

  DATABASE A_PETER_DB;

 

I suggest changing your default database, it will make the load go easier as well.

 

Note that using the DATABASE command is a temporary change, it only changes your default database until you either issue another DATABASE command or you logoff. With SQLA you can set the default database on the logon screen, this will act as your default for the session (until you logoff) or until you change it using the DATABASE command.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: Create DB & Import Excel data

Hi Peter,

 

You're permissions error relating to database DBC is because you are trying to create your table in database DBC.

 

When you logon to a TD system you are doing exactly that, logging in to the system. With some dbms's you login to a database.

 

On a TD system there will be many databases. When you issue any SQL command, if an object (e.g. tables) is not qualified by a database name the system will assume they are in your default database (temp tables may be different but we'll ignore those for now).

 

In your scenario you either change your CREATE TABLE statement to specify that table WKLY_SALES is in database A_PETER_DB :

  CREATE MULTISET TABLE A_PETER_DB.WKLY_SALES...

or you set your default database using:

  DATABASE A_PETER_DB;

 

I suggest changing your default database, it will make the load go easier as well.

 

Note that using the DATABASE command is a temporary change, it only changes your default database until you either issue another DATABASE command or you logoff. With SQLA you can set the default database on the logon screen, this will act as your default for the session (until you logoff) or until you change it using the DATABASE command.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Create DB & Import Excel data

Additional remark:

 

A DATABASE or USER in Teradata is similar to a SCHEMA in other DBMSes, as Teradata is older than Standard SQL.

Simply consider the whole Teradata system as a Database with lots of Schemas, each with an assigned disk quota (PERM SPACE) and nested in a hierarchy.

Enthusiast

Re: Create DB & Import Excel data

Thanks again Dave!

I'm good to go.

Peter

Enthusiast

Re: Create DB & Import Excel data

Thank you for explaining.

Much appreciated!

Peter