views:

211

answers:

3

Hello, I have to migrate this query (simplified here) from T-SQL to ORACLE

SET IDENTITY_INSERT table ON

INSERT INTO table (id, value) VALUES (1, 2)

SET IDENTITY_INSERT table OFF

id being an Identity field in SQLServer.

I have the same table with a sequence in ORACLE, I couldn't find a snippet that shows how to disable the sequence and set it to start again with the MAX(id) + 1.

Any ORACLE expert can help me with this?

Thanks, Rodrigo.

+3  A: 

You don't have to disable the identity in Oracle. Since you are using sequences, just don't use it for that insert.

That is, instead of

insert into table (id, values) values (table_seq.nextval, 2)

you use

insert into table (id, values) values (1, 2)

As to your second question about restarting the sequence, I think that is answered here in SO.

MJB
If you intend to keep the identity the same (which appears to be the case) this isn't a solution.
Nate Zaugg
@Nate: You missed where it says "MAX(id) +1"
OMG Ponies
+1  A: 

Messing with columns populated by Oracle sequences in this way seems like a Bad Idea. In Oracle, you are typically maintaining a column populated via sequences with a trigger. If you start turning this feature on and off, and resetting the sequence ad lib, you run the risk of a sequence not being available when another process needs it, or getting reset to a value that has been used already but not committed.

DCookie
Triggers are unnecessary as long as `sequence.NEXTVAL` is used in all INSERT statements for the table, which should really only happen in a single stored procedure anyway.
OMG Ponies
+1 to both the answer and the comment for the sane advice.
Rob van Wijk
A: 

Drop the sequences and re-create them when you're done with the max+1 value.

Nate Zaugg
assuming this is a 1-time ETL
Nate Zaugg
This invalidates code that depends on that sequence and it drops all privileges in the process as well. If that's not a problem then this is the easiest option.
Rob van Wijk
@Rob: Good point on the side effects.
DCookie