Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Insert Into >> Quick Question

    I'm trying to clone a table but with only changing the primary key value. The table is here ->


    Code:
    CREATE TABLE CUSTOMER
    (CNAME    CHAR(20) PRIMARY KEY,
     CSTREET  CHAR(20) NOT NULL,
     CCITY    CHAR(20) NOT NULL,
     CPROV    CHAR(20) NOT NULL,
     CPOSTAL  CHAR(10),
     CHPHONE  CHAR(13),
     CBPHONE  CHAR(13));
    So lets say I use the following:

    Code:
    insert into customer
    
    (select 'BILL', cstreet, ccity, cprov, cpostal, chphone, cbphone
    
       from customer
    
      where cname='JIM');
    I'm just finding the table with 'JIM' re-creating it but with 'BILL' and thus changing the primary key.. This all works (as intended) .. but my question is .. can I do this without specifying "cstreet, ccity, cprov, cpostal, chphone, cbphone" ?? The reason being is that if I use this method on a huge table with 20,50 or even 100 attributes.. then you have to type a long list of parameters

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >can I do this without specifying "cstreet, ccity, cprov, cpostal, chphone, cbphone"
    so rather than running a simple test, you decided that answers from strangers is the easiest method to obtain desired answer. Right?
    You do not need to specify the columns & then they will contain NULL values.
    If this is acceptable to you, please proceed to save time & effort by not typing the columns names.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You should specify the columns you need to popuate and then just select values for those:
    Code:
    insert into customer (cname, cstreet)
    (select 'BILL', cstreet
       from customer
      where cname='JIM');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •