CODEWITHSUNDEEP
oracle-database
Dreamer
Dreamer

Reputation: 7549

Is there any way to generate an ID without a sequence?

Current application use JPA to auto generate table/entity id. Now a requirement wants to get a query to manually insert data in to the database using SQL queries

So the questions are:

  1. Is it worth to create a sequence in this schema just for this little requirement?
  2. If answer to 1 is no, then what could be a plan b?

Upvotes: 0

Views: 171

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

Are you just doing a single data load into an empty table, and there are no other users concurrently inserting data? If so, you can just use ROWNUM to generate the IDs starting from 1, e.g.

INSERT INTO mytable
SELECT ROWNUM AS ID
      ,etc AS etc
FROM ...

Upvotes: 0

Subhas
Subhas

Reputation: 14408

Few ways:

  1. Use a UUID. UUIDs are pseudo-random, large alphanumeric strings which are guaranteed to be unique once generated.
  2. Does the data have something unique? Like a timestamp, or IP address, etc? If so, use that
  3. Combination of current timestamp + some less unique value in the data
  4. Combination of current timestamp + some integer i that you keep incrementing

There are others (including generating a checksum, custom random numbers instead of UUIDs, etc) - but those have the possibility of overlaps, so not mentioning them.

Edit: Minor clarifications

Upvotes: 3

LoztInSpace
LoztInSpace

Reputation: 5697

  1. Yes. A sequence is trivial - why would you not do it?

  2. N/A

Upvotes: 3

Related Questions