Compare Multiple Tables - Identity column

Toad for Oracle & DevOps Toolkit
1

I am trying to sync data between tables in different databases. The tables contain identity columns.

The identity columns are ignored. Is it possible to include it in the final script? The reason is I want to reuse the same values in the destination tables.

image

2

Oracle does not allow you to update identity columns. Are your columns identity in the source table and non-identity in the destination?

3

Both source and target tables have identity columns. They are basically the same tables in different databases. The identity columns are defined as “By Default”.

What I want is to use “Compare Multiple Tables” to create insert statements from source tables, which INCLUDES the identity column. Then I can run the insert statements in the target tables.

4

OK, yes, Oracle does allow you to update “by default” identity columns.

But if Toad puts some value in there, doesn’t that open up the possibility of the identity column producing a duplicate value later on?

Of course there are other ways this could happen, but here is a simple example:

  1. Start with empty tables, identity columns begin with ‘1’.
  2. 3 rows inserted into source, letting identity column populate 1,2,3.
  3. Then we copy those rows from source to target, specifying values for identity column.
  4. If more rows are later inserted into target the “normal” way, letting the identity populate its own values, then we are going to have duplicate 1, 2, and 3 in the identity column.

This is why Toad does not update identity columns.

I am OK with making a change such that if the source column is identity but the target is NUMBER, non-identity, then update the target…but it seems like we are asking for trouble by identity columns, even if they are BY DEFAULT.

Wouldn’t that become a problem for you, or am I missing something?

5

You are right that it might cause primary key violations in the example you have given. But if the target tables identify sequence has a start value of ex. 4, then a normally inserted row will just the value 5.

Maybe I should have mentioned that the target table is created as a copy of the source table (by using schema compare) so it will keep it’s sequence.

The reason is I want to keep the primary keys identical when I export data from the source to the target table. I guess my best option is to create an insert into script of the source table to get the identity column.

6

True, but what I'm saying is that if compare multiple tables (or anything else) inserts data including the identity column, at that point the sequence will be behind, and any insert after that which does not specify the identity column is going to result in a duplicate.

I guess the way around that (and maybe what you are doing) is to manually increase the sequence to a safe value every time you do an insert that specifies the identity column.

That will work, but insert scripts are slow, which is fine if you don't have much data. If you have a lot, read on.

I'll make a change for Monday's beta to sync columns that identity in source but not target. Seems like we ought to do that anyway. So if you change your target tables so that they don't use identities, then you'll be able to sync them. Unfortunately it's a bit of a pain to change, because you'd have to create a new column, copy data over to it, drop the identity column, then rename the new column).

If you don't want to do that, then here's another idea:

If your target table is emptied, you could use Schema Browser -> Right-Click -> Data -> Copy to another schema. As long as you don't have any LOB columns, this will use array DML and inserts 500 rows (by default, this value is adjustable) with each statement . I just checked it and this method will insert 'by default' identity columns.

7

Great. Thanks for your suggestions and help