INSERT SELECT will it rollback?

Database
Enthusiast

INSERT SELECT will it rollback?

Hi guys,

If I have an INSERT-SELECT statement.

Is there a scenario (aside from SET or MULTISET DDL) where my source rows will not be the same with the actual inserted rows?

Or should it always be an all or nothing result?

Thanks!

2 REPLIES
Senior Supporter

Re: INSERT SELECT will it rollback?

Hi, I am not sure that I understand the question fully.

Let's assume 

you have table TabA and TabB.

Do you mean 

Insert into TabA 

Select *

from TabB

?

No Joins, No Views etc...

Multiset to Set can reduce the row numbers - as you seem to be aware.

In teradata mode you will see all or nothing in respect to number of rows.

If you access the tabB during the insert select with locking for access you can see some rows but not all untill the commit. In case of an later rollback you have seen some records which will not be in the table later - but this is what locking for access does.

Difference can stll be on column level - for example with idenitiy columns. I am not sure if there more szenarios currently

Senior Supporter

Re: INSERT SELECT will it rollback?

If tabB contains already rows and is defined as set table you can have again lower row numbers if tabA contains rows which are allready in TabB...