Search code examples
sqlteradatateradata-sql-assistantteradatasql

Can I use union all statement with insert into statement in teradata?

I have two tables with same number of columns and same data types. I want to union then with "union all" statement and want to insert the resultant values into another table.

I am using this query

    INSERT into DB.HRfinal

select LoginID,JobTitle,Name from DB.HR1
UNION ALL
select LoginID,JobTitle,Name from DB.HR2

but it's only inserting values from first table as there are 256 rows in HR1 table and 230 rows in HR2 table but its only inserting 256 rows in HRfinal Table

Solution

  • I recreated your example and got rows from both tables in final table.

    I suspect that the problem is on union level not in insert part.

    Try:

    step 1.

    SELECT * INTO #tmp
(select LoginID,JobTitle,Name from DB.HR1
UNION ALL
select LoginID,JobTitle,Name from DB.HR2) TMP

    check if the #tmp table has all rows. If it has then:

    Step 2.

    INSERT into DB.HRfinal
SELECT * FROM #tmp