SQL Server Performance

Dividing query result into two linked tables

Discussion in 'T-SQL Performance Tuning for Developers' started by Ilya, Feb 3, 2005.

  1. Ilya New Member

    Tell me please how I can insert some set of records queried from some tables into two tables that consist of different fields and use one key field to be connected. I've tryed to use queries like
    INSERT INTO people
    (surname, stname, patronymic, foreing, gender, birthdate, fam_pos,

    dwell_type, children, nation, par_not, region, stud_fml, parn_fml, com_prob,
    sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
    SELECT STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME,

    STUDENTs_temp.INOSTR, STUDENTs_temp.SEX,
    PSPR_temp.DATA_BORN, PSPR_temp.SEM_POL, PSPR_temp.XAR_JT,

    PSPR_temp.CHILDREN, PSPR_temp.NATION,
    PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION,

    PSPR_temp.STUD_FML, PSPR_temp.PARN_FML,
    PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT,

    PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
    PSPR_temp.WG_PASPORT
    FROM STUDENTs_temp INNER JOIN
    PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
    ORDER BY STUDENTs_temp.REG_NOM
    and
    INSERT INTO students
    (reg_num, stkey, specialization, stgroup, form, enrolemt_categ,

    language, ex_prolong, sa, military_fac, category, recurr_study, enterprise, ministry,
    enrolment_date, dimissal_date, average_mark, exm_count, itm_kateg,

    itm_exmc, itm_av_mark, stgrant, stgrant_charge, difftestscount, exm_permit,
    soc_stgrant, stip_rm, hostel_dwell, num_room)
    SELECT
    students_temp.REG_NOM, students_temp.[KEY], students_temp.SPECIALIZ,

    students_temp.GRUPPA, students_temp.FORMA,
    students_temp.KATEG_Z, students_temp.[LANGUAGE],

    students_temp.CONTSESS, students_temp.SA, students_temp.WAR_KAFD,
    students_temp.KC_KATEG, students_temp.REPEATLRN,

    students_temp.FACTORY, students_temp.MINIST, students_temp.DATA_ZACH,
    students_temp.DATA_OTCH, students_temp.SR_BALL, students_temp.KOL_EXM,

    students_temp.PR_KATEG, students_temp.PR_KEXM,
    students_temp.PR_SBALL, students_temp.KC_STIP, students_temp.N_ST,

    students_temp.KOL_DIF, students_temp.DOP_SES, students_temp.SOC_Z,
    students_temp.STIP_RM, pspr_temp.LVNG_NOW, pspr_temp.NUM_ROOM
    FROM students_temp INNER JOIN
    pspr_temp ON students_temp.REG_NOM = pspr_temp.REG_NOM
    ORDER BY STUDENTs_temp.REG_NOM
    but this method of transfering data into two tables give no possibility to link them correctly. The linking field p_id the primary key of [people] table and foreing key of [students] table by such query isn't set after inserting. I've tryed to do it after but althought I've used odering the order of records in two table is different. So if I'll just add numbers as they are stored in tables they'll be linked incorrecly. Tell me please how to insert them so that they'll be linked correctly.

Share This Page