Which command would replace IDENTITY INSERT ON/OFF from SQLServer in Oracle?

oracle tsql migration sequence identity-insert
rodrigoq picture rodrigoq · May 3, 2010 · Viewed 9.7k times · Source

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.

Answer

MJB picture MJB · May 3, 2010

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.

Related questions

Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
What is the Oracle equivalent of SQL Server's IsNull() function?

In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL?

Read More
How do I declare and use variables in PL/SQL like I do in T-SQL?

In Sql Server, often times when I'm testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is. …

Read More