Link to home
Start Free TrialLog in
Avatar of lisha
lisha

asked on

error while trying to store column name in variable and insert values to it

Hi ,

 

I have a question regarding inserting values to the table..i have like 2 table T1 and T2..

T1 has 80 columns and T2 82..

both have same 79 columns ....
i need to insert data into T2 from T1 ..

since so many columns are their ..

i used this code..

 

 

declare

type col is table of varchar2(200)

index by BINARY_integer;

col_name col;

v_record T1%rowtype;

begin

select column_name bulk collect into col_name from cols where table_name like 'T2' where column_name NOT LIKE 'NAME'; --excluding the dissimilar one

select * into v_record from T1;

insert into  T2 col_name values v_record;

end;

/

 

but it throws error as insufficient value ....how to resolve this?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I really wouldn't do this the way you are trying.  I would just list all 79 columns in the insert and be done with it.

But since you asked, here is what I came up with off the top of my head:
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1), col3 char(1));

insert into tab1 values('a','a','a');
commit;

drop table tab2 purge;
create table tab2(col1 char(1), col2 char(1), col4 char(1));


declare
	v_column_list varchar2(4000);
begin
	select listagg(column_name,',') within group(order by column_name) into v_column_list from (
	select column_name from user_tab_columns where table_name='TAB1'
	intersect
	select column_name from user_tab_columns where table_name='TAB2'
	);

	execute immediate 'insert into tab2 ( ' || v_column_list || ') (select ' || v_column_list || ' from TAB1)';
end;
/

Open in new window

Avatar of lisha

ASKER

thanks a lot for your help ...this query helped a lot ....actually i have a table with 176 columns ..from where i need to retrieve 155 column ..into another table...having 168 column .....which has the column date ...as same month .....so can you suggest something else to ...any better way of doing that .... also in the execute immediate statement ...if i need to add condition ...can it be done ? Also ....how to sync them both table...like taking one column in common ..and checking if any update happend ...and if it did ..then then overwrite the row...
I would forget trying to be 'fancy' and using dynamic SQL.  It rarely works out as good as you think it will.

Is there any reason you don't want to just list the 155 columns?  I mean this will be a script of procedure so it isn't like you need to retype all 155 columns every time.

Personally I would question the design of a 176 column table.  I cannot believe it is properly normalized.  The fact that another table shares 155 of the exact same columns tells me it isn't.

Will the 'sync' always be one way from T1 to T2 or do you need to go both ways?

What happens if both tables change the same column.  Which one is the 'Master'?

I would probably do everything you want with a single MERGE statement:
http://docs.oracle.com/database/122/SQLRF/MERGE.htm#SQLRF01606
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
who cares if the list is long :)

copy all columns into notepadd++
remove some
then Ctrl+H, select extended, replace "\r\n" with ", ", click replace all

User generated image
then copy paste into your code...
and dont use dynamic query, unless it is absolutely must...
if you do not like this solution,

create v_table1 as select ...only matched columns... from table1

Open in new window


and use this view instead of table1

insert into table2 select * from v_table1

Open in new window

Avatar of lisha

ASKER

i tried doing normal insert only first ...insert into table2 select * from table1 ..but since the table 2 has more column ..it always throws and error as insufficient values... table 2 has only like monthly based data....we store the yearly based data in table 1 ...which has like lakh of record.... maybe few record changes every month.....so instead of doing partition ...we build a table 1 with necessary 155 column and and the extra column required from other table or developed by other column values in table2....so it's like a cache memory ....and other table involved are all dropped temp table...which gets truncated ...after all 168 columns are filled....but can you please tell whether it will lead to in issue the way i'm trying to do or any other factor that it should not be used .....
if those columns are at the end (if not, you can rearrange so, first columns are same) then you can use

insert into table2 select *, null, null from table1 ...

Open in new window

Avatar of lisha

ASKER

yes ,the column are at the end ....and i tried using that to... insert into table2 select *, null, null from table1 ...but i don't know why it threw error....someone told maybe table are under different schema ...so i checked that ...but we found that table are under same schema ....
Avatar of lisha

ASKER

i have actually tried to not make use of the concept of retrieving column name ...but what happened was none of the normal insert into works....so finally thought to write all column name ....and just do insert on that..then one update .... and be done...but since i have to sync them also ..... so thought to grab the column names and use them in one shot .....is their any other way out ...or maybe i'm overlooking something ..so please let me know... if normal insert into works this way ....i will be really very glad .....
dont use any dynamic qry/insert...

if  

table1 : col1, col2, ..., coln
table2 : col1, col2, ..., coln, colnp1, colnp2

then

insert into table2 select t.*, null, null from table1 t

should work...
Avatar of lisha

ASKER

thanks ...but when required to like check and update the table ...since every month the the main table t1 is loaded ..which may or may not have different values in t2 so how to give a check on n number of columns ...just to sync them ...
I thought you will just insert, not merge :)

if you want to merge, then check merge statement

MERGE INTO table2 b
    USING table1 a
    ON (a.id = b.id)
  WHEN MATCHED THEN
    UPDATE SET b.col1=a.col1, b.col2=a.col2, ...
  WHEN NOT MATCHED THEN
    INSERT (id, col1, col2, col3, ...)
    VALUES (a.id, a.col1, a.col2, a.col3, ...);

Open in new window


see details on merge here

MERGE Statement
https://oracle-base.com/articles/9i/merge-statement
Avatar of lisha

ASKER

yeah ....but i don't want an update always ...like if all column are matching just let them be ...else ....update .... can that happen?
...like if all column are matching just let them be ...else ....update ....

what does that mean?

if all columns match, do not do anything,
if just id matches update
no id match then insert?

can you give some example...
Avatar of lisha

ASKER

to be honest the struggle here is that the main table gets loaded with month ..and this column is not in the next table ....so when i do a lookup ..i lookup like ....check all values loaded for that month ...if any new value found for (check on primary key) then load ...else let it be ....don't do any update/insert...if any value is updated ...for the primary key  then update...if primary key value not found then insert....so this is the mess... :)
just create an excel

put 10 columns into table1
put 11-12 columns into table2

put some data under each

then after process, show what do you want in table2...

and upload here...

so we can understand what is happening here...
Avatar of lisha

ASKER

like i have table A having col1,col2,...col155....datecol.....coln
then table B having col1,col2...col155 ...which excludes this datecol...
lookup happens with this datecol.....
like select col1..col155 where datecol=JULY
search the table B for all the values  of col1..col155
so if matched don't do anything
if not then go ahead ..and do insert or update
still very confusing...

search the table B for all the values  of col1..col155
so if matched don't do anything

so you check 155 column to find a match?
if all values are same, do nothing
if not???? what is criteria to insert/update?

can you please create an excel an upload here...
Avatar of lisha

ASKER

sorry if confusing you...please have a look
Concept-of-table.xlsx
>>insert into table2 select t.*, null, null from table1 t

HainKurt,  the reason that will not work is BOTH tables have extra columns and only 155 in common.

If you go back to the original question:  T1 has 80 columns and T2 82..  both have same 79 columns ....

T1 has one extra column and T2 has two.

>>still very confusing...

Not really.  They want to do the MERGE which is why I already mentioned it many posts ago and also posted a working example.  Not sure what your repeating it solves.

What I have yet to get an answer on is if this is a two way merge or not and if so, which table is the "master"?
from sample data provided looks like this is the logic

check col1..col154

if there is a match and ID is different, update the ID column
if there is no match, insert it into table...

so, merge will do that

MERGE INTO table2 b
    USING table1 a
    ON (a.col1=b.col1 and a.col2=b.col2 and ... and a.col154=b.col154)
  WHEN MATCHED THEN
    UPDATE SET b.id = a.id
  WHEN NOT MATCHED THEN
    INSERT (id, col1, col2, col3, ...)
    VALUES (a.id, a.col1, a.col2, a.col3, ...);

Open in new window

>>so, merge will do that

Agreed.  Again that is why I suggested it and posted an example a long time ago.

I don't believe yours is correct.  I don't believe they want to match on ALL 154 columns.  They want to check the values in all columns based on the ID column.
Avatar of lisha

ASKER

thanks a lot .... u and HainKurt have been a great guidance..!!
Just an observation - If you're only concerned with table1 records loaded in a specific month and assuming there is a field that indicates this, you can modify your merge statement with a where clause -
merge into tab2 t2
using(
select col1, col2 from tab1
) t1
on(t1.col1=t2.col1)
when matched then update set t2.col2=t1.col2
where monthloaded = <some value>
when not matched then insert (t2.col1, t2.col2)
      values(t1.col1, t1.col2)
where monthloaded = <some value>
/
>>uou are welcome, and thanks a lot for 0 points :)

Again, your merge statement doesn't address the problem.  It will generate incorrect results.  I suggest you take my test case setup and try it.