INSERT Query with Sub-Query?

[jchastai]

Is it possible to have a SELECT query insided of an INSERT Query?

i.e. -
INSERT INTO Table1 (ID, Type, etc ...)
VALUES (1, SELECT Type FROM Table2 WHERE ...., etc ...)

I thought it sounded reasonable enough, but I am getting errors. Just an attempt to lighten the number of hits on the database.

Thanks for your help.

 

[carp]

You're close! Try this:

INSERT INTO table_1 SELECT 1, type FROM table_2 WHERE .....;

Note the lack of parentheses and the keyword VALUES.

 

[jchastai]

Okay - I am still running into problems.

If I setup the query as follows -

INSERT INTO Table1 (1, SELECT Type FROM Table2 WHERE TypeID = '1')

then I get a syntax error on the SELECT statement.

If I remove the parentheses from the above query -

INSERT INTO Table1 1, SELECT Type FROM Table2 WHERE TypeID = '1'

then I get a syntax error on the first value, 1

What am I missing?

 

[jchastai]

I may not have been real clear in my question/example.

The select query is just to provide one value in the list of values to be inserted - i.e. I am only creating one record - not inserting multiple records using a select statement.

I was trying to get around doing the following -

SELECT Type
FROM Table2
WHERE Type_ID = '1'

INSERT INTO Table1 (ID, Type)
VALUES (1, query1.Type)

Hope this is a little clearer.

Thanks for your help.

 

[carp]

Look at my first post - the syntax should look like

INSERT INTO Table1(id, type) SELECT 1, Type FROM Table2 WHERE TypeID = '1';

although it also looks like you could do

INSERT INTO table1(id,type) SELECT to_number(TypeID), Type FROM table2 WHERE TypeID='1';

If this would work and you wanted to do TypeID 1 through 5, you could do this with

INSERT INTO table1(id, type)
SELECT to_number(TypeID), Type
FROM table2
WHERE to_number(TypeID) <= 5;