Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-10-12 : 02:52:09
|
Here Iam trying to insert into 2 other tables with a unique_Id.please help me with this code tbl_emp_masterEmp_id exam_id Grade_obtained proc_st_centre contact_st proc_end_centre Contact_end issues_code issues date_of_list000100249 1903956 Grade B LA 126332 ATL 24545 D REW 10/12/20040001002492 1903956 LA 126332 ATL 24545 F MHD 11/12/20040001002492 1903956 Grade A LA 126332 ATL 24545 T YUE 12/12/20040001049121 1963794 Grade B LA 126332 ATL 24545 A YIO 10/12/2004 1963344 Grade C LA 126332 ATL 24545 D DEW 10/12/2004 1963444 Grade D LA 126332 ATL 24545 W TER 10/12/2004 1. First step I have to insert emp_id,exam_id and grade_obtained into tbl_emp_detail table which creates a emp_inc_id (identity) from the tbl_emp_master.This already I have the done SELECT w.[emp_id], W.[exam_id], W.[Grade_obtained], max(W.[date_of_list]) FROM tbl_emp_details W INNER JOIN ( SELECT emp_id,MAX(exam_id) as exam_id FROM tbl_emp_details GROUP BY emp_id )S ON (S.emp_id = W.emp_id) AND (S.exam_id = W.exam_id) AND NOT(W.emp_id IS NULL) group by w.[emp_id], W.[exam_id], W.[Grade_obtained] 2. Now when this select is done it will insert into a emp_detail table with a unique_id (emp_inc) and now with this Id I have to insert 2 recorsds into tbl_emp_locations with emp_inc_id centre contact proc_st_centre contact_st proc_end_centre contact_end Here emp_inc_id is from tbl_emp_detail for each emp_id in tbl_emp_detail |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-12 : 03:40:26
|
| guys correct me here if i'm wrong...as far as i know, you can't update two tables in one query.you should:insert into table1 theninsert into table2don't complicate the process, tackle each problem on it's own.--------------------keeping it simple... |
 |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-12 : 06:23:58
|
| yup. "keeping it simple..." is the way to go.Hemanth GorijalaBI Architect / DBA... |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-12 : 12:16:00
|
| Its not updation at the same time...First the insertion happens so that the ID is got and then with that id insert into 2 other tables..but the Id will be of tbl_emp_details and the records will be of the tbl_emp_master and tbl_master consist of multiple emp_ids |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-13 : 00:39:59
|
| inserts/updates...all the same..Insert into the first table and get the value of the IDENTITY column using @@IDENTITY and use this to insert into the other 2 tables in seperate SQL statements.I hope this is what you are trying to do..Hemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have one each.Exchange an idea, we have TWO each. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-13 : 01:42:38
|
don't get offended but the only way to learn is to explore different solutions on what you want to do, try them out and choose which will be most efficient for you.provide the query for your step#2 in your original post. and tell us if it's not working, then we might be able to help you more.  just some friendly advice... --------------------keeping it simple... |
|
|
|
|
|
|
|
|
Inserting into multiple tables