simple insert question, but can't get it

[dorothy]

Assuming that I have defaulted the SAL column of the EMP table to 1222.
How do I insert into the EMP table so that after I have querried the table by issuing SELECT * FROM EMP, 1222 will show under the SAL column?


thanks,
dorothy

[nirasha]

I was under the assumption that if you did not insert a value into the column it would take a default value:, i.e. in your insert command specify the column names you are inserting into.

good luck
nirasha

[dorothy]

Yes, I di that and it gave me an error "NOT ENOUGH VALUES".
Can you be specific in your reply?

[svk]

Do not mention that DEFAULT column in your INSERT command.

[nirasha]

Can you do a desc on your table and also paste a copy of your insert statement? I am sure we sorted this out at my work a few days ago.. I will try to help.

Good Luck,
nirasha

[chrisrlong]

The problem, as svk said, is that you must match the columns in the INSERT clause with columns in the SELECT or VALUES clause.

Therefore, if you wish to exclude a column from the INSERT, you *need* to list the other columns in the INSERT clause.

So, if you have TABLE1 with COL1 and COL2,

INSERT INTO
--TABLE1
VALUES
--(1, 2)

...works fine. However, if you wish to let Col2 use a default instead, the following *will not* work:

INSERT INTO
--TABLE1
VALUES
--(1)

The correct syntax would be:
INSERT INTO
--TABLE1
--(COL1)
VALUES
--(1)

Actually, you should *always* specify a column list for every INSERT. Correspondingly, you should *always* specify the entire column list for every SELECT (ie, SELECT * is bad!). Trust me, you will save yourself headaches down the line :)

HTH,

- Chris