Inserting values to a created table - syntax help

Database

Inserting values to a created table - syntax help

Hi All,

I'm trying to insert values into a table that I've created, utilising a sub-query.

However when running the SQL below I'm coming up with the error "3707: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and ';'." and am having trouble identifying why and how it can be corrected.

INSERT INTO custadmindb.operator_current
SELECT
Operator_ID
, Operator_Name
, Start_Date
, End_Date
, Operator_ACD_ID

FROM (SELECT
Operator_ID
, Operator_Name
, Start_Date
, End_Date
, Operator_ACD_ID

FROM prodvm.operator

WHERE Operator_ID IS NOT NULL
AND End_date IS NULL)

Any help is greatly appreciated. Thanks.
3 REPLIES
Junior Contributor

Re: Inserting values to a created table - syntax help

This is not due to the insert, the select is failing.
You just have to name the Derived Table/Inline view, only Oracle allows to omit it :-)

Dieter
Enthusiast

Re: Inserting values to a created table - syntax help

INSERT INTO DATA_QUALITY.DQ_control

SELECT

stg.JobStream

FROM (SELECT

JobStream

FROM Data_Quality.HLTH_RI_Orphan_Parent_Staging

WHERE MigrationInd = 'Y'

) as stg

The above is giving the follow error :

The positional assignment list has too few values. What positional list? I am inserting one column and retrieving one column. The target row column specs are to allow nulls in all columns.

Supporter

Re: Inserting values to a created table - syntax help


The problem is that the target table has more colums - so how should the DB system know which is the right one.


just add the target columnname after the tablename like below and it should work.


CREATE TABLE test_a_d

(pk_id integer not null, val VARCHAR(50))

unique PRIMARY INDEX (pk_id)


insert into test_a_d (pk_id) select current_date - calendar_date from sys_calendar.calendar