0

I have a table with many columns (20 or 25) and i want to build an insert statement on it, using select and generating another primary key

The simple soulution will be:

INSERT INTO myTable
SELECT mySeq.nextVal PRIMARY_KEY, COLUMN2, COLUMN3, COLUMN4...
  FROM myTable 
 WHERE PRIMARY_KEY = 1

Since my table have many columns, there is a way to say "i give you primary key, and ALL the other columns are the same" without explain them?

Like:

INSERT INTO myTable
SELECT mySeq.nextVal m.* /* Sure this not work because i get again PRIMARY_KEY column*/
  FROM myTable m
 WHERE PRIMARY_KEY = 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Mistre83
  • 2,677
  • 6
  • 40
  • 77

1 Answers1

2

There is no way to specify something like SELECT * EXCEPT aColumn, you will have to write them manually as you already did, sorry.

Actually, you could do a subquery that select column from table info then query that subquery but that make non-sense to me.

If your table contains so many column that it is a pain to write them all, then you might want to re-design your database and split your table in multiple tables.

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76