0

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

irfan gondal
  • 3
  • 1
  • This should work. Perhaps you are not executing the entire query or the second table is empty. – Gordon Linoff Sep 01 '21 at 10:58
  • I am executing entire query by selecting entire query and pressing F5. and second table is not empty. – irfan gondal Sep 01 '21 at 11:22
  • I tried using this one query too but I got error of "too many expressions in the select list of a subquery" when I run this query INSERT into DB.HRfinal select( select LoginID,JobTitle,Name from DB.HR1 UNION ALL select LoginID,JobTitle,Name from DB.HR2) – irfan gondal Sep 01 '21 at 11:25
  • @irfangonal . . . Try running the query in your question. The version in your comment is not correct. – Gordon Linoff Sep 01 '21 at 12:21
  • I have tried running that one again but same results. I think maybe structure of this query is little different in teradata, what do you say? – irfan gondal Sep 01 '21 at 12:29
  • got it. I was using union mistakenly instead of union all – irfan gondal Sep 01 '21 at 13:15

1 Answers1

0

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
Miczab
  • 112
  • 10