• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.)
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
reply
    Bookmark Topic Watch Topic
  • New Topic