Union of tables, insert in one - insert failed 3707

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Teradata Employee

Union of tables, insert in one - insert failed 3707

Hi guys,

 

Here is my query merging all sub tables in one by union of all and insert in one- 

 

SyntaxEditor Code Snippet

INSERT into TFCS_DATA.SALES_TRANSACTION as a (a.Transaction_ID, a.Location_ID, a.Transaction_Start_Dttm_DD, a.Transaction_End_Dttm_DD, a.Transaction_Date, a.Transaction_Type_CD, a.Transaction_Status_CD, a.Individual_Party_ID, a.Associate_Party_ID. a.MKB_Cost_Amt, a.MKB_Item_Qty, a.MKB_Number_Unique_Items_Qty, a.MKB_Rev_Amt, a.Visit_ID, a.Account_ID ) 

FROM 
(
   SELECT (b.Transaction_ID, b.Location_ID, b.Transaction_Start_Dttm_DD, b.Transaction_End_Dttm_DD, b.Transaction_Date, b.Transaction_Type_CD, b.Transaction_Status_CD, b.Individual_Party_ID, b.Associate_Party_ID. b.MKB_Cost_Amt, b.MKB_Item_Qty, b.MKB_Number_Unique_Items_Qty, b.MKB_Rev_Amt, b.Visit_ID, b.Account_ID) FROM  TFCS_DATA.SALES_TRANSACTION1 as b
   UNION
   SELECT (c.Transaction_ID, c.Location_ID, c.Transaction_Start_Dttm_DD, c.Transaction_End_Dttm_DD, c.Transaction_Date, c.Transaction_Type_CD, c.Transaction_Status_CD, c.Individual_Party_ID, c.Associate_Party_ID. c.MKB_Cost_Amt, c.MKB_Item_Qty, c.MKB_Number_Unique_Items_Qty, c.MKB_Rev_Amt, c.Visit_ID, c.Account_ID) FROM  TFCS_DATA.SALES_TRANSACTION2 as c
    UNION
   SELECT (d.Transaction_ID, d.Location_ID, d.Transaction_Start_Dttm_DD, d.Transaction_End_Dttm_DD, d.Transaction_Date, d.Transaction_Type_CD, d.Transaction_Status_CD, d.Individual_Party_ID, d.Associate_Party_ID. d.MKB_Cost_Amt, d.MKB_Item_Qty, d.MKB_Number_Unique_Items_Qty, d.MKB_Rev_Amt, d.Visit_ID, d.Account_ID) FROM  TFCS_DATA.SALES_TRANSACTION3 as d
    UNION
   SELECT (e.Transaction_ID, e.Location_ID, e.Transaction_Start_Dttm_DD, e.Transaction_End_Dttm_DD, e.Transaction_Date, e.Transaction_Type_CD, e.Transaction_Status_CD, e.Individual_Party_ID, e.Associate_Party_Ie. e.MKB_Cost_Amt, e.MKB_Item_Qty, e.MKB_Number_Unique_Items_Qty, e.MKB_Rev_Amt, e.Visit_ID, e.Account_ID) FROM  TFCS_DATA.SALES_TRANSACTION4 as e
    UNION
   SELECT( f.Transaction_ID, f.Location_ID, f.Transaction_Start_Dttm_DD, f.Transaction_End_Dttm_DD, f.Transaction_Date, f.Transaction_Type_CD, f.Transaction_Status_CD, f.Individual_Party_ID, f.Associate_Party_If. f.MKB_Cost_Amt, f.MKB_Item_Qty, f.MKB_Number_Unique_Items_Qty, f.MKB_Rev_Amt, f.Visit_ID, f.Account_ID) FROM  TFCS_DATA.SALES_TRANSACTION5 as f);

 

I shows error insertp-error.png \\

What needs to be corrected here? 


Accepted Solutions
Junior Contributor

Re: Union of tables, insert in one - insert failed 3707

If you get a No more room in database error you must increase the perm space assigned to the target database :-)

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Union of tables, insert in one - insert failed 3707

The "as a" is wrong, there's no INSERT FROM and there are way to many parens:

 

INSERT into TFCS_DATA.SALES_TRANSACTION (a.Transaction_ID, a.Location_ID, a.Transaction_Start_Dttm_DD, a.Transaction_End_Dttm_DD, a.Transaction_Date, a.Transaction_Type_CD, a.Transaction_Status_CD, a.Individual_Party_ID, a.Associate_Party_ID. a.MKB_Cost_Amt, a.MKB_Item_Qty, a.MKB_Number_Unique_Items_Qty, a.MKB_Rev_Amt, a.Visit_ID, a.Account_ID ) 
   SELECT b.Transaction_ID, b.Location_ID, b.Transaction_Start_Dttm_DD, b.Transaction_End_Dttm_DD, b.Transaction_Date, b.Transaction_Type_CD, b.Transaction_Status_CD, b.Individual_Party_ID, b.Associate_Party_ID. b.MKB_Cost_Amt, b.MKB_Item_Qty, b.MKB_Number_Unique_Items_Qty, b.MKB_Rev_Amt, b.Visit_ID, b.Account_ID FROM  TFCS_DATA.SALES_TRANSACTION1 as b
   UNION
   SELECT c.Transaction_ID, c.Location_ID, c.Transaction_Start_Dttm_DD, c.Transaction_End_Dttm_DD, c.Transaction_Date, c.Transaction_Type_CD, c.Transaction_Status_CD, c.Individual_Party_ID, c.Associate_Party_ID. c.MKB_Cost_Amt, c.MKB_Item_Qty, c.MKB_Number_Unique_Items_Qty, c.MKB_Rev_Amt, c.Visit_ID, c.Account_ID FROM  TFCS_DATA.SALES_TRANSACTION2 as c
    UNION
   SELECT d.Transaction_ID, d.Location_ID, d.Transaction_Start_Dttm_DD, d.Transaction_End_Dttm_DD, d.Transaction_Date, d.Transaction_Type_CD, d.Transaction_Status_CD, d.Individual_Party_ID, d.Associate_Party_ID. d.MKB_Cost_Amt, d.MKB_Item_Qty, d.MKB_Number_Unique_Items_Qty, d.MKB_Rev_Amt, d.Visit_ID, d.Account_ID FROM  TFCS_DATA.SALES_TRANSACTION3 as d
    UNION
   SELECT e.Transaction_ID, e.Location_ID, e.Transaction_Start_Dttm_DD, e.Transaction_End_Dttm_DD, e.Transaction_Date, e.Transaction_Type_CD, e.Transaction_Status_CD, e.Individual_Party_ID, e.Associate_Party_Ie. e.MKB_Cost_Amt, e.MKB_Item_Qty, e.MKB_Number_Unique_Items_Qty, e.MKB_Rev_Amt, e.Visit_ID, e.Account_ID FROM  TFCS_DATA.SALES_TRANSACTION4 as e
    UNION
   SELECT f.Transaction_ID, f.Location_ID, f.Transaction_Start_Dttm_DD, f.Transaction_End_Dttm_DD, f.Transaction_Date, f.Transaction_Type_CD, f.Transaction_Status_CD, f.Individual_Party_ID, f.Associate_Party_If. f.MKB_Cost_Amt, f.MKB_Item_Qty, f.MKB_Number_Unique_Items_Qty, f.MKB_Rev_Amt, f.Visit_ID, f.Account_ID FROM  TFCS_DATA.SALES_TRANSACTION5 as f;

 Do you really need UNION, UNION ALL would yield much better performance?

Btw, when you switch to .NET instead of ODBC SQL Assistant will also show you the position of an syntax error.

 

Teradata Employee

Re: Union of tables, insert in one - insert failed 3707

Thank you :)

It works now, executes query but table is not populated. It shows less space is DB error... :(

 

SyntaxEditor Code Snippet

insert into TFCS_DATA.SALES_TRANSACTION (Transaction_ID, Location_ID, Transaction_Start_Dttm_DD, Transaction_End_Dttm_DD,Transaction_Date,Transaction_Type_CD, Transaction_Status_CD, Individual_Party_ID, Associate_Party_ID, MKB_Cost_Amt, MKB_Item_Qty, MKB_Number_Unique_Items_Qty, MKB_Rev_Amt, Visit_ID, Account_ID)

select Transaction_ID, Location_ID, Transaction_Start_Dttm_DD, Transaction_End_Dttm_DD,Transaction_Date,Transaction_Type_CD, Transaction_Status_CD, Individual_Party_ID, Associate_Party_ID, MKB_Cost_Amt, MKB_Item_Qty, MKB_Number_Unique_Items_Qty, MKB_Rev_Amt, Visit_ID, Account_ID from TFCS_DATA.SALES_TRANSACTION1 
union all
select Transaction_ID, Location_ID, Transaction_Start_Dttm_DD, Transaction_End_Dttm_DD,Transaction_Date,Transaction_Type_CD, Transaction_Status_CD, Individual_Party_ID, Associate_Party_ID, MKB_Cost_Amt, MKB_Item_Qty, MKB_Number_Unique_Items_Qty, MKB_Rev_Amt, Visit_ID, Account_ID from TFCS_DATA.SALES_TRANSACTION2
union all
select Transaction_ID, Location_ID, Transaction_Start_Dttm_DD, Transaction_End_Dttm_DD,Transaction_Date,Transaction_Type_CD, Transaction_Status_CD, Individual_Party_ID, Associate_Party_ID, MKB_Cost_Amt, MKB_Item_Qty, MKB_Number_Unique_Items_Qty, MKB_Rev_Amt, Visit_ID, Account_ID from TFCS_DATA.SALES_TRANSACTION3
union all
select Transaction_ID, Location_ID, Transaction_Start_Dttm_DD, Transaction_End_Dttm_DD,Transaction_Date,Transaction_Type_CD, Transaction_Status_CD, Individual_Party_ID, Associate_Party_ID, MKB_Cost_Amt, MKB_Item_Qty, MKB_Number_Unique_Items_Qty, MKB_Rev_Amt, Visit_ID, Account_ID from TFCS_DATA.SALES_TRANSACTION4
union all
select Transaction_ID, Location_ID, Transaction_Start_Dttm_DD, Transaction_End_Dttm_DD,Transaction_Date,Transaction_Type_CD, Transaction_Status_CD, Individual_Party_ID, Associate_Party_ID, MKB_Cost_Amt, MKB_Item_Qty, MKB_Number_Unique_Items_Qty, MKB_Rev_Amt, Visit_ID, Account_ID from TFCS_DATA.SALES_TRANSACTION5;

 

Junior Contributor

Re: Union of tables, insert in one - insert failed 3707

If you get a No more room in database error you must increase the perm space assigned to the target database :-)