Inserting from Exceptions table

[isonlyme]

Hi all!

Can I use select statements in the value section of the Insert into format?

I want to do the following:

insert into table1 (Name, CUSTOMERID)
Values ('Javier', select customerid from tracking_arc where rowid in (select row_id from exceptions))

and add all the records countinuosly like a loop until there are no more records on the exception table.

there are 6019 records in the exception table and i only want the row_id value and i dont want to replace manually the value on the insert statement 6019 times....

Can this be done?

Thanks in advance!!!!appreciate any help

 

[PruSQLer]

Interesting idea you came up with but when I tried this in my DBMS (DB2), it didn't let me do it. I would just run 2 simple queries to accomplish this.

First:

Insert into table1 (CUSTOMERID)
Select customerid from tracking_arc where rowid in (select row_id from exceptions);

Second:

Update table1 (Name)
Set Name = 'Javier';

 

[r937]

i think just one step is better --

insert into table1
(Name, CUSTOMERID)
select 'Javier', customerid
from tracking_arc
where rowid in
(select row_id from exceptions)

rudy

http://rudy.ca/

 

[isonlyme]

ok, sounds good but, I'll give you an example:

Table1 contains

101010100232, 99, 0,0,36,crm

the exceptions table has:

101010100238
198272882829
228377383990

I need to insert the 3 records from the exceptions table into table1 and for everu record i update on the table 1 I need the values 99,0,0,36,crm for each

so the result on table1 will be:

101010100238, 99, 0,0,36,crm
198272882829, 99, 0,0,36,crm
228377383990, 99, 0,0,36,crm

I hope this explain more in detail...maybe my previous example was a little off cause i dont Have a FIXED value to so a SET or SELECT 'JAVIER'....

let me know, Thanks

 

[r937]

where is

99,0,0,36,crm

coming from? is it the only value in Table1? the last? is it always constant? if all the columns get these values, why are they even columns in the table? if they always start out as a certain default value, why aren't the columns declared with default clauses?

 

[isonlyme]

im inserting dummy records to enable a constraint, the 99,0,0,36,crm are values that im inserting just to fill the requiered fields the only value that changes is the customerid (the 1010100232). I normally do this for 1 or 2 records but this time im missing 6019 records:

as you can see this is the actual statement that i run to fix the error "parent keys not found" when i try to enable a FK on the tracking_arc table, the first value is the one that i need in the exceptions table the rest are dummy values i always insert

insert into contact_arc (
CONTACTID,
CSUSERID,
STARTDATETIME,
CUSTOMERNUMBER,
CUSTOMERCONTROL1,
CUSTOMERCONTROL2,
CUSTOMERCONTROL3,
CUSTOMERCONTROL4,
CUSTOMERAPPLICATION,
SECONDSDURATION,
DEPOSITTABCOUNT,
SAVINGSTABCOUNT,
CONSUMERLOANTABCOUNT,
CUSTOMERTABCOUNT,
SERVICECOUNT,
SALESCOUNT,
DEBITCARDTABCOUNT,
LAST_SYS_UPDATE,
ARCHIVEDATETIME)
values('00125200104261927300339', -->>value i need from exception table
195,
TO_DATE('2001-09-03 23:59:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN' ),
00000000099999,
0001,
0000,
0000,
0000,
'RM',
2099,
0,
0,
0,
3,
3,
0,
0,
TO_DATE('2001-09-03 23:59:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'),
TO_DATE('2001-09-03 23:59:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')
)

hope this help...thanks!!!!

 

[r937]

if they are dummy values that you always insert, consider defining them as DEFAULT values for the columns

that way you can leave them out of the INSERT statement and they will get those values

in any case, the sql i gave previously should work

use INSERT SELECT instead of INSERT VALUES

replace 'Javier' with the constants you want inserted

rudy

 

[isonlyme]

I will try That and let you know

Thanks!!!Rudy

 

[isonlyme]

Thanks rudy it worked perfectly!!!

thanks for the great help all!!!