CODEWITHSUNDEEP
sqloracle-database
Danger_Fox
Danger_Fox

Reputation: 449

Oracle INSERT INTO with select and values

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,

);

Upvotes: 0

Views: 13610

Answers (2)

xQbert
xQbert

Reputation: 35323

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.

Upvotes: 7

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

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.

Upvotes: 1

Related Questions