CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005
Dharmendra Kumar Singh
Dharmendra Kumar Singh

Reputation: 2991

Looping each record of a table and inserting data into a temp table joining another temp table in SQL

I have two temporary table #CoreUtilizationForRole1 and #RoleID2Details. I want to insert values into a third temp table using these two temp table. Below are the table Structure of both the Temp table.

create table #CoreUtilizationForRole1(roleid int,SupervisorName varchar(50),ImmediateSupervisor varchar(50),UserECode varchar(50),UserName nvarchar(250),Designation varchar(250),TimeSpent float,ActivityName varchar(250))

create table #RoleID2Details(UserECode varchar(50),UserName nvarchar(250))

in #CoreUtilizationForRole1 table I had generated a report and stored in it. #RoleID2Details contains data as mentioned below in the query:-

insert into #RoleID2Details
    select distinct Userecode,Username from #CoreUtilizationForRole1 where roleid=2

Now I want to loop through each records of #RoleID2Details and insert data into a third temp table while joining from #CoreUtilizationForRole1. AcTually I need data in a particular described format and due to that, this looping is required so that I insert data into third temp table as per my requirement and then using a simple select statement I will display it.

The third table Structure will be -

create table #ThirdtempTable(roleid int,SupervisorName varchar(50),ImmediateSupervisor varchar(50),UserECode varchar(50),UserName nvarchar(250),Designation varchar(250),TimeSpent float,ActivityName varchar(250))

and the logic for inserting data into #ThirdtempTable is like this looping each records of #RoleID2Details table-

insert into #ThirdtempTable
Select A.RoleId,A.SuperVisorName,A.Userecode,A.Username,A.Designation,A.TimeSpent,A.ActivityName 
from #CoreUtilizationForRole1 A 
inner join #RoleID2Details B 
on
A.SuperVisorName=B.UserName
where B.UserECode= --First UserECode from #RoleID2Details

insert into #ThirdtempTable
Select A.RoleId,A.SuperVisorName,A.Userecode,A.Username,A.Designation,A.TimeSpent,A.ActivityName 
from #CoreUtilizationForRole1 A 
inner join #RoleID2Details B 
on
A.UserName=B.UserName
where A.UserECode=--First UserECode from #RoleID2Details

this looping will go until we extract all the records from #RoleID2Details table and insert it as mentioned above into #ThirdtempTable table.

Upvotes: 0

Views: 1772

Answers (1)

AnandPhadke
AnandPhadke

Reputation: 13486

insert into #your3rdTempTable
select distinct t1.UserECode,t1.UserName from
#CoreUtilizationForRole1 t1 join #RoleID2Details t
on t1.UserECode=t2.UserECode and t1.UserName = t2.UserName

You can try this way:

insert into #ThirdtempTable
Select A.RoleId,A.SuperVisorName,A.Userecode,A.Username,A.Designation,A.TimeSpent,A.ActivityName 
from #CoreUtilizationForRole1 A 
inner join #RoleID2Details B 
on
A.SuperVisorName=B.UserName
where B.UserECode=A.UserECode

insert into #ThirdtempTable
Select A.RoleId,A.SuperVisorName,A.Userecode,A.Username,A.Designation,A.TimeSpent,A.ActivityName 
from #CoreUtilizationForRole1 A 
inner join #RoleID2Details B 
on
A.UserName=B.UserName
where A.UserECode=A.UserECode

Upvotes: 2

Related Questions