CODEWITHSUNDEEP
sharepointms-access-2010volatileteradata
John Smith
John Smith

Reputation: 2886

Create a Volatile table in teradata

I have a sharepoint list which i have linked to in MS Access. The information in this table needs to be compared to information in our datawarehouse based on keys both sets of data have. I want to be able to create a query which will upload the ishare data into our datawarehouse under my login run the comparison and then export the details to Excel somewhere. MS Access seems to be the way to go here.

I have managed to link the ishare list (with difficulties due to the attachment fields)and then create a local table based on this. I have managed to create the temp table in my Volatile space. How do i append the newly created table that i created from the list into my temporary space.

I am using Access 2010 and sharepoint 2007

Thank you for your time

Upvotes: 0

Views: 5644

Answers (1)

DrBailey
DrBailey

Reputation: 183

If you can avoid using Access I'd recommend it since it is an extra step for what you are trying to do. You can easily manipulate or mesh data within the Teradata session and export results. You can run the following types of queries using the standard Teradata SQL Assistant:

CREATE VOLATILE TABLE NewTable (

column1 DEC(18,0),

column2 DEC(18,0)

)

PRIMARY INDEX (column1)

ON COMMIT PRESERVE ROWS;

Change your assistant to Import Mode (File-> Import Data)

INSERT INTO NewTable (?,?)

Browse for your file, this example would be a comma delineated file with two numeric columns and column one being the index. You can now query or join this table to any information in the uploaded database.

When you are finished you can drop with:

DROP TABLE NewTable

You can export results using File->Export Data as well.

If this is something you plan on running frequently there are many ways to easily do these type of imports and exports. The Python module Pandas has simple functionality for reading a query directly into DataFrame objects and dropping those objects into Excel through the pandas.io.sql.read_frame() and .to_excel functions.

Upvotes: 1

Related Questions