• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 36
  • Last Modified:

Two forms of an insert statement. Are both equivalent, and acceptable?

I am working with a stored procedure that is used about four times per year to update a table in the database. Basically the script contains a bunch of insert statements that insert data into a particular Oracle table. Now when I write insert statements I like to list out all of the column names and values being inserted into a column. See line #1 below. I noticed that the last time the script was updated by a former team member that instead of listing out all of the column names for the insert, that they use a SELECT dual statement to select data into the table; see line #2. So (Question 1) are both insert statements acceptable? If so then is one method better than the other? (Question 2).



#1 INSERT INTO CUSTOMER (ID,FirstName,LastName) VALUES (109, 'Joe','Smith');

#2 INSERT INTO CUSTOMER SELECT 109,'Joe','Smith' FROM DUAL;
0
brgdotnet
Asked:
brgdotnet
2 Solutions
 
schwertnerCommented:
They both will insert one row in the table.
There is a small difference between them.
In the first one you declare the columns - (ID,FirstName,LastName)
In the second one the declaration of the columns is missing and in some cases this can cause errors.
0
 
ste5anSenior DeveloperCommented:
To compare them, you should address the column list order problem:

Either

#1 INSERT INTO CUSTOMER VALUES (109, 'Joe','Smith');
#2 INSERT INTO CUSTOMER  SELECT 109,'Joe','Smith' FROM DUAL;

or

#1 INSERT INTO CUSTOMER (ID,FirstName,LastName) VALUES (109, 'Joe','Smith');
#2 INSERT INTO CUSTOMER (ID,FirstName,LastName) SELECT 109,'Joe','Smith' FROM DUAL;

In both cases the second is more text to type. And while not physically possible, but logical, the second query may insert nothing or too much. A table may be empty or return more than one row.

Imho the second style is coding bad style.
0
 
brgdotnetcontractorAuthor Commented:
Thanks guys. I gave schwertner more points because he answered first.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now