All Forums Database
anniemalec 3 posts Joined 03/11
16 May 2011
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.

dnoeth 4628 posts Joined 11/04
16 May 2011

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

Dieter

i5commuter 10 posts Joined 01/12
23 Feb 2012

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.

ulrich 816 posts Joined 09/09
24 Feb 2012

 

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.