Dividing query result into two linked tables | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dividing query result into two linked tables

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.
]]>