StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInserting records in temporary table in sql server?
text
Body
copied!<p>In SQL Server, I declare one table and trying to insert records, but it is taking so much time to insert. This is my temp table :</p> <pre><code> declare @totalAprovals Table( apptype varchar(max) , Id varchar(max) , empno varchar(max) , empname varchar(max) , AppliedDate varchar(max) , rstatus varchar(max) , LeaveType varchar(max) , fromdate varchar(max) , todate varchar(max) , finyear varchar(max) , noofdays varchar(max) , perdate varchar(max) , pertype varchar(max) , TotMin varchar(max) , FrmTime varchar(max) , ToTime varchar(max) , ConDate varchar(max) , Amount varchar(max) , MaterialDesc varchar(max) , EstValue varchar(max) , FromYear varchar(max) , ToYear varchar(max) , AvailedFrom varchar(max) , AvailedTo varchar(max) , Purpose varchar(max) , FromPlace varchar(max) , ToPlace varchar(max) , ICode varchar(max) , IDesc varchar(max) , MgrId varchar(max) </code></pre> <p>)</p> <p>and my insert statement :</p> <pre><code> insert into @totalAprovals SELECT DISTINCT 'LEAVE' AppType , CRS.applicationId ID , CRS.EmpId EmpNo , ISNULL(( SELECT FirstName FROM Tbl_Emp_M WHERE EmpId=CRS.EmpId ) , CRS.EmpId) EmpName , CONVERT(VARCHAR(10),LA.LeaveDate,103) AppliedDate , (CASE ISNULL(( SELECT top 1 CurStatus FROM Tbl_CRS_Leave_AppHis_T WHERE stepno=CRS.StepNo-1 and applicationId=CRS.applicationId AND Status=1 order by StepNo desc),'0') WHEN '0' THEN 'Applied' WHEN '1' THEN 'Recommended' WHEN '2' THEN 'Approved' END) Rstatus , LT.LeaveName LeaveType , CONVERT(VARCHAR(10),LA.FromDate,103) FromDate , CONVERT(VARCHAR(10),LA.ToDate,103) ToDate , '' FinYear , '' NoOfDays , '' PerDate , '' PerType , '' TotMin , '' FrmTime , '' ToTime , '' ConDate , 0 Amount , '' MaterialDesc , 0 EstValue , '' FromYear , '' ToYear , ''AvailedFrom , '' AvailedTo , '' Purpose , '' FromPlace , '' ToPlace , '' ICode , '' IDesc , CRS.MgrId FROM Tbl_Leave_App_T LA , Tbl_CRS_Leave_App_T CRS , Tbl_Leave_Typ_M LT , Tbl_Emp_ServiceDetails_T EMS WHERE CRS.applicationId = LA.ApplicationId AND LA.LeaveTypeId = LT.LeaveTypeId and crs.EmpId = ems.EmpId AND CRS.Status = 1 AND LA.Status = 1 AND LT.Status = 1 and ems.Status = 1 AND CRS.CurStatus IN ('0') AND YEAR(LA.LeaveDate) = YEAR(GETDATE()) AND la.LeaveTypeId not in (9,12) AND -- LA.ApplicationId LIKE '%LEV%' AND CRS.EmpId = EMS.EmpId and ems.LocationCode IN ('101','102','103','104','AHUP') and crs.MgrId ='xxxxx' </code></pre> <p>It is taking 2 to 3 minutes to execute this. What could be the reason? Am I writing wrong process to insert records?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload