All Forums Analytics
NMadson-3173 44 posts Joined 09/06
07 Sep 2006
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;

Barry-1604 176 posts Joined 07/05
08 Sep 2006

insert into tbl_amt (acct_ky,as_of_dt,amt,insert_dt_tm)SELECT acct_ky,'2006-01-01',3.53,CURRENT_TIMESTAMPfrom tbl_acctWHERE src_sys = 'A';

leo.issac 184 posts Joined 07/06
12 Sep 2006

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

bhaskar06 1 post Joined 07/12
24 Jul 2012

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

ulrich 816 posts Joined 09/09
24 Jul 2012

"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!

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Qaisar Kiani 337 posts Joined 11/05
24 Jul 2012

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)

littlefoot 1 post Joined 07/12
24 Jul 2012

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

vasudev 24 posts Joined 12/12
14 Nov 2013

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?

Raja_KT 1246 posts Joined 07/09
14 Nov 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

vasudev 24 posts Joined 12/12
15 Nov 2013

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?

You must sign in to leave a comment.