I am writing SP to generate dynamic SQL query which creates insert statements.
I want for a particular column the data should be fetched from another table. I have tried sample and top but teradata doesnt allow sub queries with sample statements.
Here is the sample scenario which I am trying to achieve
INSERT INTO TABLE_A
VALUES ( 1,(SEL COL1 FROM TABLE_X SAMPLE 1),(SEL COL1 FROM TABLE_Y SAMPLE 1));
Is there any work around to solve the above issue.
Thanks in Advance.
You obviously need to change your approach of writting INSERT/SELECT query. Easier approach should be to write one SELECT statement first, before generating it in SP dynamically.
Generate something like
SELECT 1, A.Col1, B.Col1 From Table1 A , Table2 B;
And then simply use it in INSERT.
For further details, please provide sample data for the tables you mentioned.
Thanks for the reply. Here is the sample data that I am trying to achieve.
Cust_id Cust_type_CD Cust_Location
1 Ind Newyork
2 Org Washington
Ind Individual Customer
Org Organization Customer
Location Id Location
I want random Cust_typ_cd and Location to be inserted to the Table A.
Please let me know how to achieve, the above result.
I can rewrite the SQL statement incase I have only one table(say Table X or Y alone), But the number of columns like this decided dynamically based on the table structure. I cant like the table X and Y as they are not related to each other(Reference tables).
I have written SP to pass the parameters of the table and column for which the data and from which the sample record will be sent back to the calling procedure.
Like the below
You can have one SELECT as below:
The Above query produces random number of records.
Sometimes it's not producing any and some times multiple records are coming. But I need only one value for Cust_type_CD and Cust_Location value for the PK 1.
Just do a cross join of derived tables:
INSERT INTO TABLE_A
select 1, a.col1, b.col1
(SEL COL1 FROM TABLE_X SAMPLE 1) as a,
(SEL COL1 FROM TABLE_Y SAMPLE 1) as b;
I have a query which selects the top SELECT top 180000 records exported to a file and it takes upto 1 hour. How can I optimize this. Are there any features that SQL teradata Java edition would support.
How can I imcrease the buffer size in a teradataSQl assistant?