RE: Insert without telling column names
I've managed to get it through my programmers heads that they have to ALWAYS specify column names in any DML statement. Since they work in the same development database, and often need changes made to the same parent table, this prevents the "WHO SCREWED UP MY PROGRAM?" questions >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Insert without telling column names >Date: Tue, 20 Mar 2001 14:18:13 -0800 > > >Well of course it's a bad idea, I thought >that was obvious. ;) > >Jared > >On Tue, 20 Mar 2001, Jacques Kilchoer wrote: > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > > > > > How can I create an insert statement without telling all the > > > names of the columns? > > > Give an example, please. > > > > > > As Jared mentioned, if you are inserting into all the columns in a >table, > > you don't need to specify the column names. But this is a BAD IDEA, >because > > if someone adds a column to the table all your insert statements written > > without column names will fail unless you add values for the new column >to > > the insert statement. So while this might be OK when typing in SQL for > > "one-time" use, don't use this method in any programs/scripts that will >be > > used more than once. > > > > -- > > any ignorant comments made are the sole responsibility of J. R. Kilchoer >and > > should not reflect adversely upon my employer. > > > > Jacques R. Kilchoer > > (949) 754-8816 > > Quest Software, Inc. > > 8001 Irvine Center Drive > > Irvine, California 92618 > > U.S.A. > > http://www.quest.com > > > > > > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Insert without telling column names
Well of course it's a bad idea, I thought that was obvious. ;) Jared On Tue, 20 Mar 2001, Jacques Kilchoer wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > > > How can I create an insert statement without telling all the > > names of the columns? > > Give an example, please. > > > As Jared mentioned, if you are inserting into all the columns in a table, > you don't need to specify the column names. But this is a BAD IDEA, because > if someone adds a column to the table all your insert statements written > without column names will fail unless you add values for the new column to > the insert statement. So while this might be OK when typing in SQL for > "one-time" use, don't use this method in any programs/scripts that will be > used more than once. > > -- > any ignorant comments made are the sole responsibility of J. R. Kilchoer and > should not reflect adversely upon my employer. > > Jacques R. Kilchoer > (949) 754-8816 > Quest Software, Inc. > 8001 Irvine Center Drive > Irvine, California 92618 > U.S.A. > http://www.quest.com > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Insert without telling column names
Hi all -
I stand (sit?) before you with egg on my face. I KNOW you can insert
without naming column names if you're filling the entire row, I just
didn't consider that this might be what Roland was attempting to
accomplish. As Regina and Jacques both pointed out, if your package,
procedure or script is to be reused, it's probably best not to assume
the table structure you think will be there actually is there.
With humble apologies,
David A. Barbour
Oracle DBA
Formerly with the now defunct and bankrupt ConnectSouth
William Beilstein wrote:
>
> You are misinformed. If you have a table of the form
>
> col1varchar2(1)
> col2 number
> col3 date
>
> You can insert into it with the insert statement
> INSERT INTO MYTABLE VALUES('X',23,SYSDATE);
>
> As long as ALL the columns are included in the values clause in the order they were
>created in the table, you don't have to define the column names.
>
> >>> [EMAIL PROTECTED] 03/20/01 11:36AM >>>
> Roland,
>
> You can't. There are a number of ways to really cut down on the code
> required to do an insert, but if you think about it, how will Oracle
> know where the data is supposed to go if you don't give it the column
> name(at some point)?
>
> If you're using 8i, there is a direct load insert, but as far as I'm
> aware, this will work only for a select * from another table.
>
> Dynamic SQL works pretty well for this kind of stuff. What are you
> trying to do?
>
> Regards,
>
> David A. Barbour
> Oracle DBA
> Formerly with the now defunct and bankrupt ConnectSouth
>
> [EMAIL PROTECTED] wrote:
> >
> > Hallo,
> >
> > How can I create an insert statement without telling all the names of the columns?
> > Give an example, please.
> >
> > Roland Sköldblom
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California-- Public Internet access / Mailing Lists
> >
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: David A. Barbour
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: William Beilstein
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David A. Barbour
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Insert without telling column names
You are misinformed. If you have a table of the form
col1varchar2(1)
col2 number
col3 date
You can insert into it with the insert statement
INSERT INTO MYTABLE VALUES('X',23,SYSDATE);
As long as ALL the columns are included in the values clause in the order they were
created in the table, you don't have to define the column names.
>>> [EMAIL PROTECTED] 03/20/01 11:36AM >>>
Roland,
You can't. There are a number of ways to really cut down on the code
required to do an insert, but if you think about it, how will Oracle
know where the data is supposed to go if you don't give it the column
name(at some point)?
If you're using 8i, there is a direct load insert, but as far as I'm
aware, this will work only for a select * from another table.
Dynamic SQL works pretty well for this kind of stuff. What are you
trying to do?
Regards,
David A. Barbour
Oracle DBA
Formerly with the now defunct and bankrupt ConnectSouth
[EMAIL PROTECTED] wrote:
>
> Hallo,
>
> How can I create an insert statement without telling all the names of the columns?
> Give an example, please.
>
> Roland Sköldblom
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David A. Barbour
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: William Beilstein
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Insert without telling column names
At 08:36 AM 3/20/01 -0800, you wrote:
>Roland,
>
>You can't. There are a number of ways to really cut down on the code
>required to do an insert, but if you think about it, how will Oracle
>know where the data is supposed to go if you don't give it the column
>name(at some point)?
Of course you can. The only caveat is Oracle will assume you are filling
all the columns in order, therefore you must provide a value for every
column, ie
For Table T1(firstname, lastname, telephone, fax, email)
insert into T1 values('John','Smith','4849910','','[EMAIL PROTECTED]')
Note the '' in place of a fax number. I could also have used null.
I never use this method in applications or procedures, of course, because
then if you change the table definition it no longer works, but for testing
stuff, or something I'm only going to run once it's a decent shortcut.
>If you're using 8i, there is a direct load insert, but as far as I'm
>aware, this will work only for a select * from another table.
>
>Dynamic SQL works pretty well for this kind of stuff. What are you
>trying to do?
>
>Regards,
>
>David A. Barbour
>Oracle DBA
>Formerly with the now defunct and bankrupt ConnectSouth
>
>[EMAIL PROTECTED] wrote:
> >
> > Hallo,
> >
> > How can I create an insert statement without telling all the names of
> the columns?
> > Give an example, please.
> >
> > Roland Sköldblom
> >
> > --
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Regina Harter
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Insert without telling column names
Roland,
Check out the SQL manual, the syntax diagram clearly illustrates that you
don't need to explicitly name the column names as long as you are inserting
into all of the columns in the order they are defined in the data
dictionary.
insert into table_A values ('Hi', 'there');
A word of friendly caution -- the list will rapidly recognize you by name
and fail to answer questions when it is obvious that you haven't taken the
time to 1) try it yourself and 2) read the docs. Also, you need to give
more information in your queries. We realize that Oracle is a big, bad
world and there is a lot to know, but most of us don't have the time to
answer every question every newbie has. I, of course, am an exception at
the moment since I am just killing time this week waiting to be laid off...
Regards,
Diana
-Original Message-
Sent: Tuesday, March 20, 2001 9:31 AM
To: Multiple recipients of list ORACLE-L
Hallo,
How can I create an insert statement without telling all the names of the
columns?
Give an example, please.
Roland Sköldblom
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Diana Duncan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Insert without telling column names
Title: RE: Insert without telling column names > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > How can I create an insert statement without telling all the > names of the columns? > Give an example, please. As Jared mentioned, if you are inserting into all the columns in a table, you don't need to specify the column names. But this is a BAD IDEA, because if someone adds a column to the table all your insert statements written without column names will fail unless you add values for the new column to the insert statement. So while this might be OK when typing in SQL for "one-time" use, don't use this method in any programs/scripts that will be used more than once. -- any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer. Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com
Re: Insert without telling column names
Here is what my sqlplus training (Oracle) says: Inserting New Rows Insert a new tow containing values for each column. Optionally lisgt the columsn in the INSERT clause. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, March 20, 2001 9:31 AM Hallo, How can I create an insert statement without telling all the names of the columns? Give an example, please. Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Insert without telling column names
On Tue, 20 Mar 2001 [EMAIL PROTECTED] wrote: > Hallo, > > How can I create an insert statement without telling all the names of the columns? > Give an example, please. > > Roland Sköldblom > Roland, You can omit column names only if you are inserting into all columns of the table, in the same order that they appear in the data dictionary. e.g. create table mytab ( col1 number, col2 date, col3 number); valid: insert into mytab values(1, sysdate, 2); invalid: insert into mytab values( 1, sysdate ); ERROR at line 1: ORA-00947: not enough values invalid: insert into mytab values( 1,2,sysdate); ERROR at line 1: ORA-00932: inconsistent datatypes Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Insert without telling column names
Roland, You can't. There are a number of ways to really cut down on the code required to do an insert, but if you think about it, how will Oracle know where the data is supposed to go if you don't give it the column name(at some point)? If you're using 8i, there is a direct load insert, but as far as I'm aware, this will work only for a select * from another table. Dynamic SQL works pretty well for this kind of stuff. What are you trying to do? Regards, David A. Barbour Oracle DBA Formerly with the now defunct and bankrupt ConnectSouth [EMAIL PROTECTED] wrote: > > Hallo, > > How can I create an insert statement without telling all the names of the columns? > Give an example, please. > > Roland Sköldblom > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David A. Barbour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Insert without telling column names
Hallo, How can I create an insert statement without telling all the names of the columns? Give an example, please. Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
