Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    55

    Unhappy Unanswered: inserting values for alias names within tables?

    hi every1

    I have a database table in which one of the fields is an alias for the identity field. That is the alias field self references the table and takes its value for the "id" field of tht table
    as follows:
    system_id int
    system_name varchar(20)
    sys_alias int
    sys_pro varchar(80)
    sys_values varchar(80)
    so here the sys_alias takes wtever value the system assigns to the id field, system_id while inserting values into the table..
    now my problem is i have to insert records into ths table frm aother tables using insert into...select from statements bt since the id values r enerated by the comp during value insertion i dunno how to give values fr the alias?

    e.g insert into my_table(system_name,sys_alias,sys_pro,sys_values)
    select x,(how to give ths field),y,z
    from another_table
    where....


    neone who understood my problem and can help puhllezz post me a reply asap!
    thnx

    shuchi
    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: inserting values for alias names within tables?

    Sounds like you'd have to do this in a trigger.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the expression for the "alias" column? is it exactly the same value, or is it something like a character prefix plus the value?

    perhaps you might consider using a view instead


    rudy

  4. #4
    Join Date
    Jan 2003
    Posts
    55
    hi...
    its exactly the same value..jus like a copy column fr the identity column..
    i tried using max(syb_identity) and @@identity functions but they do not work specially since i put them in a sub query so theyre not calculated recursivelye but just once and insert the same identity value for all other inserted records!
    can anyone help me what sortof view i shud create?
    i am doing ths whole process thru a perl script so whatever sql tht needs to be done is done thru an sql file run from my perl script. mebbe asome sorta trigger will work tht automatically inserts the newest value of identityt generated into the alias field while insertion of records?
    gosh i really need help here....so any ideas wud be gr8
    thnx fr all the suggestions

    -shuchi
    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if this "alias" column is to have exactly the same values, then you don't really need it

    just select it twice in any query --
    Code:
    select system_id
         , system_name
         , system_id   as sys_alias
         , sys_pro
         , sys_values 
      from yourtable
    rudy

  6. #6
    Join Date
    Jan 2003
    Posts
    55
    thanx a ton fr all the help...managed it

    -shuchi
    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

Posting Permissions

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