sql insert error

[phil2phil]

Hi,
I'm working on an asp where I wanted to do a simple insert using data
from another table, structure is:
Table A has columns - ID, Code, Type
Table B has columns - key, FName, LNAme, DOB, cd, etc...

I need to insert into A, using only the 'key' field from B, rest of the
values in A will be hardcoded in (code='XYZ', TYPE='A')

I tried this:

insert into TableA
values
(select key from TableB where cd='app', 'XYZ', 'A')

but it didn't work, I kept getting an error 'Missing Expression', using
Oracle 9i.

Also tried speicifying the columns

insert into TableA (ID, Code, Type)
values
(select key from TableB where cd='app', 'XYZ', 'A')

same error. Could someone help me with the insert.

Thanks.

 

[Jevon]

This isn't the right group for this - you should be asking in an Oracle
group as it has nothing to do with asp. However, your query is wrong for
what you're trying to do. I have minimal experience with Oracle, but would
suggest something like the following:

INSERT INTO TableA (ID, Code, Type)
VALUES
(SELECT key AS ID, 'XYZ' AS Code, 'A' AS Type FROM TableB WHERE cd='app')

If that works, or is at least what you're trying to do, I would suggest
reading up on [Oracle] SQL syntax.

Jevon

 

[Patrice]

IMO try:
INSERT INTO TableA SELECT Key,'XYZ','A' FROM TableB

Values if or constants only...

 

[phil2phil]

Thanks to both for the help, i tried the below one first and i worked,
thanks again!