Home > IT Answers > Database > Change the size of a column and then reorganize the rows in SQL tables
Hispi
25 pts.
 Change the size of a column and then reorganize the rows in SQL tables
INSERT SELECT statement, INSERT statement, SQL Database, SQL Server, SQL tables
I think this one will be tricky.
What I have is a table like:
CREATE TABLE PHOTO_DETAIL
  (
    "PHOTO"        VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "NUM_LINE"   NUMBER(20,0) NOT NULL ENABLE,
    "PHOTO_LINE"  VARCHAR2(180 BYTE)
  )
 
what I am going to have is:
CREATE TABLE PHOTO_DETAIL
  (
    "PHOTO"        VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "NUM_LINE"   NUMBER(20,0) NOT NULL ENABLE,
    "PHOTO_LINE"  VARCHAR2(1024 BYTE)
  )
And what I need is to reorganize the rows. 
Photo, is simply the name of the Photo.
Num_line identifies the line of a Photo.
Photo_line is simply the part of data that a Photo haves.
This is because I stored a picture in multiple rows.
Now, I need to increase the PHOTO_LINE size so each Photo will take less rows to be selected.
But i dont find how to do it.
I have tried with a 
INSERT INTO PHOTO_DETAIL (DE.FOTO, DE.LINEA_FOTO, DE.NUM_LINEA)
select ?????
but dont know how exactly select the data to be stored.
Thank You in Advance


Software/Hardware used:
SQL
ASKED: July 27, 2010  12:13 PM
UPDATED: August 2, 2010  9:39 AM
RELATED QUESTIONS
Answer Wiki:
Last Wiki Answer Submitted:  Be the first to answer this question.
All Answer Wiki Contributors:  Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

What database (DBMS) are you using?

For the actual change, I might run something like: 

ALTER TABLE PHOTO_DETAIL ALTER COLUMN PHOTO_LINE
   SET DATA TYPE VARCHAR ( 1024 BYTE)

Depending on the number of rows, I might add an ALLOCATE() clause that fits the distribution of data.

I might also run: 

UPDATE PHOTO_DETAIL SET PHOTO_LINE = trim(PHOTO_LINE)

…just to get variable length values into all existing rows.

For your INSERT, it’s unclear whether DE.NUM_LINEA is CHAR or VARCHAR or what its values might look like. I thought maybe you could do TRIM(DE.NUM_LINEA) rather than the direct value, but it could depend on the data.

Tom

TomLiotta
 110,135 pts.
 

is this sql server?

Rick Martinez
 585 pts.
 

Its Oracle 11g but we also work with sqlserver, but If you can help me with the Oracle one I may can fix the sqlserver.

I have googled for ALLOCATE(),this is what,from my eyes, would fix what I need but I havent find usefull information. My sqldeveloper regonize that instruction but I dont know how use it. If you could teach me plz.

Thanks for answering

Hispi
 25 pts.
 

Finally, i could not make work this. I had to do it throught csharp inside of my application.

Thank you anyway.

Hispi
 25 pts.