| How to INSERT WHOLE RECORD IN ONE MOMENT to table? [message #38892] |
Fri, 24 May 2002 01:51  |
Tanya
Messages: 3
Registered: May 2002
|
Junior Member |
|
|
HI!I have A RECORD my_rec with 100 fields inside.
I want to INSERT the RECORD to table without typing 100 field names , how can I do it?
1.INSERT INTO my_table
VALUES (my_rec.field1,my_rec.field2...,my_rec.field100)
- it not seams to me a good way,
2. but the following does not work:
INSERT INTO my_table
VALUES (my_rec);
Thanks in advance.
|
|
|
|
| Re: How to INSERT WHOLE RECORD IN ONE MOMENT to table? [message #38894 is a reply to message #38892] |
Fri, 24 May 2002 02:17  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
To avoid the typeing you can get a comma separated list of the column names by doing
SELECT decode(rownum,1,'',',')||column_name
FROM user_tab_columns
WHERE table_name = .....;
If you are doing this insert in Pl/Sql and you'll do it in more than one place, you should create a procedure to do the insert. That way you only have to type in the column names once, and you can write the procedure so that you can pass in a record-type.
|
|
|
|
| Re: How to INSERT WHOLE RECORD IN ONE MOMENT to table? [message #38908 is a reply to message #38892] |
Sat, 25 May 2002 17:06 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
As you need to enter the values for all columns in a row to be inserted, you have to enter the values in an INSERT statement. Instead of typing the values every time you run an INSERT, you better use & for substitution. So every time you run that same INSERT statement, it will ask you the values to be inserted interactively.
For example,
instead of typing INSERT INTO MYTABLE VALUES (1,2,3...100) every time with different 100 values,
give like this INSERT INTO MYTABLE VALUES (&V1,&V2,&V3...&V100) run again and again without changing anything in that INSERT statement. It just asks the values interactively for each &Vn substitution and stores them in respective columns and inserts the record into the table. Keep in mind that, if the column is a string type, the subsitution should be enclosed in single quotes like '&V1'.
Store the INSERT statement in buffer and run it again and again by just entering R at SQL*Prompt.
Hope this helps.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
