CODEWITHSUNDEEP
sql-serverteradata
user2058350
user2058350

Reputation: 29

Run query on sql server through teradata and store result in teradata

I have one table in SQL server and 5 tables in Teradata.I want to join those 5 table in teradata with sql server table and store result in Teradata table.

I have sql server name but i dont know how to simultaneously run a query both on sql server and teradata.

i want to do this: sql server table query

Select distinct store 
from store_Desc

teradata tables:

select cmp_id,state,sde 
from xyz 
where store in (
select distinct store 
from sql server table)

Upvotes: 0

Views: 3592

Answers (2)

DrBailey
DrBailey

Reputation: 183

You can create a table (or a volatile table if you do not have write privileges) to do this. Export result from SQL Server as text or into the language of your choice.

CREATE VOLATILE TABLE store_table ( column_1 datatype_1, column_2 datatype_2, ... column_n datatype_n); You may need to add ON COMMIT PRESERVE ROWS before the ; to the above depending on your transaction settings. From a language you can loop the below or do an execute many.

INSERT INTO store_table VALUES(value_1, value_2, ..., value_n);

Or you can use the import from text using Teradata SQL Assistant by going to File and selecting Import. Then execute the below and navigate to your file.

INSERT INTO store_table VALUES(?, ?, ..., n);

Once you have inserted your data you can query it by simply referencing the table name.

SELECT cmp_id,state,sde FROM xyz WHERE store IN( SELECT store FROM store_table)

The DISTINCT function is most easily done on export from SQL Server to minimize the rows you need to upload.

EDIT: If you are doing this many times you can do this with a script, here is a very simple example in Python:

import pyodbc con_ss = pyodbc.connect('sql_server_odbc_connection_string...') crs_ss = con_ss.cursor() con_td = pyodbc.connect('teradata_odbc_connection_string...') crs_td = con_td.cursor() # pull data for sql server data_ss = crs_ss.execute(''' SELECT distinct store AS store from store_Desc ''').fetchall() # create table in teradata crs_td.execute(''' CREATE VOLATILE TABLE store_table ( store DEC(4, 0) ) PRIMARY INDEX (store) ON COMMIT PRESERVE ROWS;''') con_td.commit() # insert values; you can also use an execute many, but this is easier to read... for row in data_ss: crs_td.execute('''INSERT INTO store_table VALUES(?)''', row) con_td.commit() # get final data data_td = crs_td.execute('''SELECT cmp_id,state,sde FROM xyz WHERE store IN( SELECT store FROM store_table);''').fetchall() # from here write to file or whatever you would like.

Upvotes: 1

xenodevil
xenodevil

Reputation: 604

Is fetching data from the Sql Server through ODBC an option?

The best option may be to use Teradata Parallel Transporter (TPT) to fetch data from SQL Server using its ODBC operator (as a producer) combined with Load or Update operator as the consumer to insert it into an intermediate table on Teradata. You must then perform rest of the operations on Teradata. For the rest of the operations, you can use BTEQ/SQLA to store the results in the final Teradata table. You can also put the same SQL in TPT's DDL operator instead of BTEQ/SQLA and get it done in a single job script.

To allow use of tables residing on separate DB environments (in your case SQL-Server and Teradata) in a single select statement, Teradata has recently released Teradata Query Grid. But I'm not sure about exact level of support for SQL-Server and it will involve licensing hassle and quite a learning curve to do this simple job.

Upvotes: 0

Related Questions