INSERT SELECT and other values

Analytics
Enthusiast

INSERT SELECT and other values

Can anyone tell me what the correct syntax would be when I'm attempting to insert one value using a SELECT statement and the other values as fixed? Using the syntax below, I get an error message, "3706: Syntax error: expected something between the 'VALUES' keyword and the 'SELECT' keyword. Output directed to Answerset window"

insert into tbl_amt
(acct_ky,
as_of_dt,
amt,
insert_dt_tm)
VALUES
SELECT acct_ky from tbl_acct
WHERE src_sys = 'A',
'2006-01-01',
3.53,
CURRENT_TIMESTAMP;
9 REPLIES
Enthusiast

Re: INSERT SELECT and other values

insert into tbl_amt
(acct_ky,
as_of_dt,
amt,
insert_dt_tm)
SELECT acct_ky,
'2006-01-01',
3.53,
CURRENT_TIMESTAMP
from tbl_acct
WHERE src_sys = 'A';
Enthusiast

Re: INSERT SELECT and other values

Barry is correct.The correct syntax could be insert into [column list] select [column list] from table;

Re: INSERT SELECT and other values

Can somebody help me. I am trying to insert

Insert into dbc.xxx (a,b,c) values (select * from abc,null,null).

I want this query to get inserted into table not the value which we get after executing it.

Kindly help ASAP

Supporter

Re: INSERT SELECT and other values

"not the value which we get after executing it"

What do you mean with this?

You are mixing two different systax types of the insert stement.

you can use insert into tab values (val1,val2, ... valx); 

for single row inserts

or insert into tab select col1, ..., colx from tabsource;

for insert selects.

Check the manuals!

Enthusiast

Re: INSERT SELECT and other values

Encapsulate the SQL query in the commas and treat it as a string...

Insert into dbc.xxx (a,b,c) values ('select * from abc',null,null)

Re: INSERT SELECT and other values

it might give error if the table abc contains morethan one column/three columns,

and the query can be

insert into dbc.xxx(a,b,c) select * from abc;  --if abc contains 3 column

insert into dbc.xxx(a,b,c) select a,null,null from abc;  --if abc contains 1 column

Enthusiast

Re: INSERT SELECT and other values

Hi,

Can someone tell me the advantage of using insert select into the empty table. Whether its I/o wise useful or CPU wise useful?

Enthusiast

Re: INSERT SELECT and other values

Hi,

As per my understanding, since there is no TJ/WAL maintenance, because your target table is empty, so it  follows the fast-path. So from that standpoint it is fast.

For the second point, I feel it depends on your data. If highly skewed, then I feel that CPU will rise. Why don't you see in PMON for your data and verify?

Cheers,

Raja

Enthusiast

Re: INSERT SELECT and other values

Hi Raja,

Thanks for your valuable comments.

In my case i am going to add two new columns into the huge table of 200 million rows. So i am planning to rename the existing table and create a table with new structure the use insert select to load the table. In this case which one will be affected I/O or CPU?