I am getting a spool error while inserting records from one table to another of similar structure expect for one column where table 1 has a column with char(50) and some 63 compression values
and table 2 has same column with char(120) with 63 compression values.(here I made sure that maximum column size here to be 7560 bytes)
When i tried inserting values using query insert into tab2 select top 1 * from tab1 I am getting no more spool space error.
But I am not getting this error when tab2 has the same column datatype as that of tab1 as char(50)
When you do INSERT-SELECT between 2 exact same tables then there is NO spool involved at all. That might be the reason when you match the datatype in table2 with table1, you do not get the spool space error.
Regarding getting the spool space error, there must be bad PI and/or PPI in these tables. Check the data in table once and make sure data is getting distributed evenly (as much as possible) among the amps. Also, if you are using your own id then either you can ask DBA to provide more spool space to your id or you can use some ETL id to do that operation as ETL ids usually have enough spool space allocated to them to perform such operations.