Change the size of a column and then reorganize the rows in SQL tables

25 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • Rick Martinez
    is this sql server?
    585 pointsBadges:
    report
  • Hispi
    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
    25 pointsBadges:
    report
  • Hispi
    Finally, i could not make work this. I had to do it throught csharp inside of my application. Thank you anyway.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following