• Post Reply Bookmark Topic Watch Topic
  • New Topic

Oracle query to create 50 copies of single row into the same table with different Student_id  RSS feed

 
SunilK Chauhan
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Guys,

I have one table called 'Student' and having 30 columns including student_id as primary key.
Now i have one entry already there in table with 'student_id' = 1. (defines one student data)

Now i want to create one sql query to copy all the data same from the row, but with different student_id as it is primary key defined there.
(Note: it will create 50 different students entry into table automatically with the same data)

How is it possible?
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 37253
519
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose I have this table with one row:


To copy that row with a different id (2), you can write:



With 30 fields, you just have more columns to include in the select clause. (You might want to consider updating your table design; that's a log of columns.)
 
Dave Tolls
Rancher
Posts: 2914
36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What version of Oracle are you using?
If it's 12 then there is an IDENTITY type that will handle the id incrementing for you.

If it's earlier, then I would hope you had a SEQUENCE for that ID which you use on INSERT.

So your INSERT would be something like:


Now, you can then stick that in a PL/SQL loop to create 50 of them.
You might even be able to come up with a way of not hardcoding the 50, but I wouldn't worry too much about that!
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!