Oracle INSERT INTO with select and values

Question

I'm trying to insert some values into a table from a select statement and some hardcoded values, but I'm not sure what the syntax would be. When I try I get an error saying there are not enough values so I know it's not reading my select statement correctly. Any help is appreciated.

insert into INSERT_STG
values(
  (select code,
          acct,  
          to_char(sysdate, 'mmddyy'),
          amt      
  from schema.table),
  'Partners',
  'city',
  'st',
  'Y',
  null,

);

xQbert · Accepted Answer · 2013-11-15 20:45:13Z

7

insert into INSERT_STG
  (select code,
          acct,  
          to_char(sysdate, 'mmddyy'),
          amt ,
  'Partners',
  'city',
  'st',
  'Y',
  null
  from schema.table);

Problems:

You had extra comma after null
You can't combine hardcoded values and the select like you did. The hard coded values have to be generated as part of the select.

This should work assuming: INSERT_STG has 9 columns of the datatypes in schema.table in the order of the select and string and support null on last column.

edited Nov 15, 2013 at 20:45

answered Nov 15, 2013 at 20:34

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danger_Fox Over a year ago

Thanks, that was it! I just wasn't sure how all of that worked in pl/sql.

LINQ2Vodka · Accepted Answer · 2013-11-15 20:30:10Z

1

Get rid of the "values" line and ensure you're inserting the same count of values as the table INSERT_STG has. Otherwise, explicitly specify columns of target table to insert.

answered Nov 15, 2013 at 20:30

LINQ2Vodka

3,0362 gold badges31 silver badges49 bronze badges

Collectives™ on Stack Overflow

Oracle INSERT INTO with select and values

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related