Default vs Override INSERT Signatures

INSERT statements seem simple enough; however, they still seem to give beginners a problem (even the vets have brain farts and get caught up with them). When you are employing an INSERT statement, you can use one of two methods: the Default Signature or the Override Signature.

The Default Signature assumes that you will be inserting data into ALL columns in the target table; therefore when you create an INSERT statement, you do not need to specify which columns you will be inserting into. You will, however, need to provide data for ALL columns in that table. Let's assume that you have created a table in Oracle with the following structure:

CREATE TABLE test
( test_id NUMBER
, test_name CHAR(10)
, test_date DATE
, CONSTRAINT pk_test PRIMARY KEY (test_id));

In MySQL, the same table can be created with the following code:

CREATE TABLE test
( test_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, test_name VARCHAR(10)
, test_date DATE );

Using the Default Signature, you would simply insert the data without specifying the columns:

INSERT INTO test
VALUES -- Single-row Inserts MUST have the VALUES keyword
( 1
, 'LSAT'
, '10-MAR-2013' );

Using the Override Signature, you would need to specify BEFORE the VALUES keyword which columns you're inserting into; you will also need to ensure that the columns match up:

INSERT INTO test
( test_id
, test_name
, test_date )
VALUES -- Single-row Inserts MUST have the VALUES keyword
( 2
, 'ACT'
, '23-JUL-2013' );

When I say 'ensure that the columns match up', I mean that the columns in the Override Statement have to correspond to the data that you're inserting after the VALUES keyword.

You'll notice in my code samples that I mentioned a Single-row Insert. A Single-row Insert is exactly what it sounds like - an INSERT statement that inserts a single row of data into the target table. We won't get into Multiple-row Inserts now, but we'll go over them in the next post. We'll also cover the differences in syntax between Oracle and MySQL INSERT statements.

I hope you've found this post helpful, and thank you for visiting!

Posted in: 10g,11g,default signature,insert,MySQL,Oracle,override signature,sql,xe