Insert using Select

[LesleyW]

I am trying to insert values into one table based on a subquery on another. The problem is I need to combine literals with results from the subquery to make the new rows.

So I tried this

insert into cust_functions
(SELECT 'CODE1', cust_id, 'LWTest', sysdate
FROM customers
WHERE cust_type_cd = 'distributor');

CODE1 and LWTest are the literals I require in columns 1 & 3. Column 2 contains a unique ID from another table, and column 4 is the current date/time.

If I put the literals in quotes, it tries to use these values including the quotes - which fails because column 1 is constrained to a value in another table. (otherwise I would put it in with quotes and do an update to replace them afterwards)

If I leave out the quotes it thinks CODE1 and LWTest are column names and I get 'invalid column name' error.

I've tried using Select in combination with the VALUES keyword and it doesn't like that either -

e.g.
SQL> insert into cust_functions
2 (cust_function_type_cd,
3 cust_id ,
4 user_id ,
5 date_time_chg)
6 Values
7 ('CODE1',
8 SELECT cust_id from customers WHERE cust_type_cd = 'distributor',
9 'LWTest',
10 sysdate);

and I get
SELECT cust_id from customers WHERE cust_type_cd = 'distributor',
*
ERROR at line 8:
ORA-00936: missing expression

Do I have to get the select statement and iterate through each row? If so how do I do this?

 

[desiindian]

Hi,

Try the following :
insert into cust_functions
(SELECT 'CODE1' <colname>, cust_id, 'LWTest' <colname1>, sysdate
FROM customers
WHERE cust_type_cd = 'distributor');

Replace colname and colname1 with the actual column names from the target table cust_functions.

Hope this helps

 

[LesleyW]

Thanks I have just tried this but it still tries to use 'CODE1' (including quotes) as the value for <colname>, resulting in an integrty constraint error. And if I try it without the quotes, it still interprets CODE1 and LWTest as column names and complains about invalid column names.

 

[srishan]

You do not need parenthesis around the (select) when you are inserting. Also you do not need column aliases in the select, try this,

INSERT INTO cust_functions
SELECT 'CODE1', cust_id , 'LWTest', sysdate
FROM customers
WHERE cust_type_cd = 'distributor';

P.S I am assuming that you are inserting into all columns.
Thx,
Sri

 

[LesleyW]

Sri - Thanks for your input, I tried your code but leaving out the brackets and the column names does not make any difference. Exactly the same results with and without quotes.

 

[LesleyW]

Woops, mea culpa.

The reason for the constraint error was not because of the '' but because it was causing duplicate values in the cust_functions table. I created a new value instead of CODE1 so there were definitely no duplicates, and it worked.

Thanks for all your input.

 

[jee]

I have a question - in what context is the SQL statement being executed - from SQL*PLUS, from PL/SQL, from a VB app, or what?? I do this kind of thing all of the time from PL/SQL and SQL*PLUS and the format described by SRISHAN works.

Also, since you don't list the column names on the insert, you have to make sure your columns are in the correct order. To ensure the proper ordering I would do this:

INSERT INTO cust_functions (code, cust_id, desc, cust_date)
SELECT 'CODE1', cust_id , 'LWTest', sysdate
FROM customers
WHERE cust_type_cd = 'distributor';

Obviously I have made up column names for your table, since I don't know what they are.

 

[LesleyW]

Jee I think we posted simultaneously! I did realise that all columns needed values and I just put them in in the order they come up under 'desc' results. I did not know you could list the columns like that.

As you said the format described by SRISHAN works.

Cheers