INSERT INTO SELECT causing data to double

Question

I have strange issue. I am working in a Teradata database. In a procedure I am populating a main table by processing data via multiple intermediate tables. But in the end only one table is populating the main table. The syntax is something like this

INSERT INTO sch_1.main_table
(
col1,
col2...
coln
)
SELECT
col1,
col2...
coln
FROM sch_1.work_table;

Now the count(*) from sch_1.work_table results in 112rows whereas its going straight to double in sch_1.main_table i.e. 224 rows.

I am unable to share the logic of the procedure as its for a client. But this thing has me stumped. There are no joins involved in this statement, just a plain and simple insert from a work table to a main table.

Has anyone faced this?

Answer 1

Might be caused by a trigger on the target table

Answer 2

I am new to sql but try SELECT DISTINCT instead of just SELECT. It worked for me.

Answer 3

You could also try just using a SELECT INTO syntax instead of using the fully specified INSERT INTO. That would eliminate any potential mismatch between the two statements. So you'd just do this:

SELECT
    col1,
    col2...
    coln
INTO sch_1.main_table
FROM sch_1.work_table;