Multiple insert with unique ID

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple insert with unique ID

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-10 : 15:06:08

[code]Emp_id exam_id Grade_obtained proc_st_centre contact proc_end_centre Contact issues_code issues date_of_list
000100249 1903956 Grade B LA 126332 ATL 24545 D REW 10/12/2004
0001002492 1903956 LA 126332 ATL 24545 F MHD 11/12/2004
0001002492 1903956 Grade A LA 126332 ATL 24545 T YUE 12/12/2004
0001049121 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[/code]
This is the data set I have in a table The thing i have to do is :
1. select em_id,exam_id,grade_obtained to one table emp_details with maximum exam_id and the emp_id should not be null
Spirit helped me with this code to make it more fast:
select DISTINCT emp_id,exam_id, Grade_obtained
from Temp_table R
inner join (SELECT MAX(exam_id) as exam_id, emp_id FROM Temp_table group by emp_id ) S
on (S.emp_id = R.emp_id) and (S.exam_id = R.exam_id) and (R.emp_id is not null)

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 emp_locations with
[code]emp_inc centre contact
(From emp_detail) proc_st_centre contact
(From emp_detail) proc_end_centre contact [/code] This is for each emp_id with the same criteria above with maximum exam_id and the emp_id should not be null

3. Again the 3rd part is to insert issue_code,issues and date_of_list into emp_exam_issue for all the emp_id which is
not NULL with the emp_inc id from the emp_detail table
So it will be
[code] emp_inc issues_code issues date_of_list
(From emp_detail) D REW 10/12/2004
(From emp_detail) F MHD 11/12/2004
(From emp_detail) T YUE 12/12/2004[/code] This is for each emp_id

Here I may need to use the cursor and this will decrease the speed dramatically...PLease help me with this issue

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-10 : 15:15:14

well... this would be easier if you post Create table and insert into statements (sample data)
and desired result based on sample data.
because i don't quite get what you mean.

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-10 : 18:04:32

insert into temp_table
(
0001049121,1963794,Grade B,LA,126332,ATL,24545, A,YIO,10/12/2004
)

insert into temp_table
(
NULL,1963344,Grade C,LA,126332,ATL,4545,D,DEW,10/12/2004

)

insert into temp_table
(
NULL, 1963444,Grade D,LA,26332,ATL,24545,W,TER,10/12/2004

)

These are the insert statements for the temp-table.

The desired ouput for the 1st table is

emp_details :
emp_inc(unique-identity),Emp_id,exam_id,Grade_obtained

The desired output for the second table is
emp_locations

emp_inc centre location contact_details
emp_inc Start proc_st_centre contact
emp_inc End proc_end_centre contact

Here the emp_inc is from emp_details as a record is inserted in emp_details a emp_inc is generated a
and that is used to creste 2 records for each emp_id is created in emp_loactions

Here the select is same as for the emp_details and emp_locatoins with the maximum exam_id and maximum date_of_list
where emp_id is not null

Now the 3rd part is as I mentioned for emp_exam_issue table is first posting

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-10 : 20:13:42

3. Again the 3rd part is to insert issue_code,issues and date_of_list into emp_exam_issue for all the emp_id which is
not NULL with the emp_inc id from the emp_detail table
So it will be

emp_inc issues_code issues date_of_list
(From emp_detail) D REW 10/12/2004
(From emp_detail) F MHD 11/12/2004
(From emp_detail) T YUE 12/12/2004
This is for each emp_id

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-10 : 22:27:45

Can we not use cursors and populate these tables accordingly.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-11 : 04:02:41

please suggest me a solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources