Databases
StackOverflow2013
Postsdbo
POINSERT ERROR: Select isn't matching definition of columns
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POINSERT ERROR: Select isn't matching definition of columns
    primarykey
    data
    text
    <p>I have a query that ranks call centers based on a couple of performance metrics. I am attempting to modify this query by adding in the number of surveys each center received. </p> <p>After adding the "Total" or "Surveys" column in the appropriate places, I am receiving the error:</p> <pre><code>Insert Error: Column name or number of supplied values does not match table definition. </code></pre> <p>Can you tell me where I am mis-defining the column? I will post the relevant parts of the code:</p> <pre><code>IF OBJECT_ID('tempdb..#ACSResults') IS NOT NULL DROP TABLE #ACSResults CREATE TABLE #ACSResults (AreaID VARCHAR(4), Location VARCHAR(50), Surveys VARCHAR(6), MonthName VARCHAR(6), RepResolve FLOAT, ERP FLOAT) INSERT INTO #ACSResults SELECT a.area, a.location, COUNT(a.IVRCallID) as Surveys, a.monthname, CASE WHEN SUM(CASE WHEN a.RepResolve IN ('1','0') THEN 1 ELSE 0 END) = 0 THEN NULL ELSE CAST(SUM(CASE WHEN a.RepResolve = '1' THEN 1 ELSE 0 END)AS FLOAT) / CAST(SUM(CASE WHEN a.RepResolve IN ('1','0') THEN 1 ELSE 0 END) AS FLOAT) END AS REPRESOLVE, CASE WHEN SUM(CASE WHEN a.ERP IN ('0','1','2','3','4','5','6','7','8', '9', '10') THEN 1 ELSE 0 END) = 0 THEN NULL ELSE (CAST(SUM(CASE WHEN a.ERP IN ('8', '9', '10') THEN 1 ELSE 0 END) AS FLOAT) / CAST(SUM(CASE WHEN a.ERP in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10') THEN 1 ELSE 0 END) AS FLOAT))END AS ERP FROM (SELECT ... acs.IVRCallID, ... FROM dbCustomerSurvey.Detail.vwAfterCallSurvey acs LEFT JOIN dbEmployee.Summary.vwEmployeeHistory eh ON acs.EmployeeID = eh.EmployeeID AND acs.OfferDate BETWEEN eh.StartDate AND eh.EndDate LEFT JOIN dbEmployee.Config.vwName Rep ON eh.EmployeeID = Rep.EmployeeID LEFT JOIN dbEmployee.Config.vwName Sup ON eh.BottomUp01ID = Sup.EmployeeID LEFT JOIN dbEmployee.Config.vwName Mgr ON eh.BottomUp02ID = Mgr.EmployeeID LEFT JOIN dbEmployee.Config.vwName Dir ON eh.BottomUp03ID = Dir.EmployeeID LEFT JOIN dbEmployee.Config.vwVirtualLocation vl ON eh.VirtualLocationID = vl.VirtualLocationID LEFT JOIN dbEmployee.Config.vwDepartment d ON eh.DepartmentID = d.DepartmentID LEFT JOIN dbEmployee.Config.vwPeopleSoftDepartment psd ON eh.PeopleSoftDepartmentID = psd.PeopleSoftDepartmentID WHERE acs.CurrentStatus IN ('Completed', 'COMP') and acs.surveytype IN ('ACS_FCR_Rep_Perform', 'ACS_FCR_Rep_Perform-prepaid', 'ACS_Rep_Perform_BSC2', 'ACS_Rep_Perform_Gov') AND acs.OfferDate Between @StartDate AND @EndDate) a ... group by a.area, a.location, a.monthname --------------------------------------------------------------------------------REPEAT TABLE------------------------------------------------------------------------------------------------------ IF OBJECT_ID('tempdb..#HRRep') IS NOT NULL DROP TABLE #HRRep CREATE TABLE #HRRep (AreaID VARCHAR(4), Location VARCHAR(50), Surveys VARCHAR(6), MonthName VARCHAR(6), HourRepeatPercent FLOAT) INSERT INTO #HRRep (AreaID, Location, Surveys, MonthName, HourRepeatPercent) Select eh.AreaID, vl.VirtualLocationDescription, COUNT(acs1.IVRCallID) as Surveys, Month (acs.statdate) AS MonthName, Cast(Sum(acs.Repeats2Hr)as float) /nullif(Sum(acs.Calls2Hr), 0) as 'HourRepeatPercent' From dbReportSummary.ReportSummary.vwRepeatCalls2Hr acs with (NoLOCK) LEFT JOIN dbEmployee.Summary.vwEmployeeHistory eh with (NoLOCK) ON acs.EmployeeID = eh.EmployeeID AND acs.StatDate BETWEEN eh.StartDate AND eh.EndDate LEFT JOIN dbEmployee.Config.vwVirtualLocation vl ON eh.VirtualLocationID = vl.VirtualLocationID LEFT JOIN dbEmployee.Config.vwDepartment d ON eh.DepartmentID = d.DepartmentID LEFT JOIN dbEmployee.Config.vwPeopleSoftDepartment psd ON eh.PeopleSoftDepartmentID = psd.PeopleSoftDepartmentID LEFT JOIN dbCustomerSurvey.Detail.vwAfterCallSurvey acs1 ON acs.EmployeeID=acs1.EmployeeID and acs1.OfferDate BETWEEN @StartDate and @EndDate ... Group by eh.AreaID, vl.VirtualLocationDescription, Month (acs.statdate) SELECT a.Location, a.Surveys, a.RepResolve, a.RRRank, a.ERP, a.ERPRank,a.HourRepeatPercent, a.RepeatRank, (a.RRRank + a.ERPRank+a.RepeatRank) as 'Total Rank Points', Rank() OVER(ORDER BY (a.RRRank + a.ERPRank+a.RepeatRank) ASC, a.RepResolve DESC) AS 'Overall Rank' From( SELECT a.Location, a.Surveys, a.RepResolve, Rank() OVER(ORDER BY a.RepResolve DESC) AS RRRank, a.ERP, Rank() OVER(ORDER BY a.ERP DESC) AS ERPRank, b.HourRepeatPercent, Rank() OVER(ORDER BY b.HourRepeatPercent ASC) AS RepeatRank FROM #ACSResults AS A Left Join #HRRep AS B on a.areaid=b.areaid and a.monthname=b.monthname and a.location=b.location and a.Surveys=b.Surveys ) a Group BY a.Location, a.Surveys, a.RepResolve, a.RRRank, a.ERP, a.ERPRank,a.HourRepeatPercent, a.RepeatRank, (a.RRRank + a.ERPRank+a.RepeatRank) </code></pre> <p>"..." denotes a chunk of code I deleted due to irrelevance. Please let me know if I have not posted enough. I can post more but heard complaints about posting too much in the past. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USciw916
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COis This the code that "fails"? `INSERT INTO #HRRep (AreaID, Location, Surveys, MonthName, HourRepeatPercent)`
      singulars
      1. POINSERT ERROR: Select isn't matching definition of columns
      1. USPablo Claus
    2. COIt's actually 'INSERT INTO #ACSResults' that's giving me the error. Not sure if #HRRep would be related since we're joining the 2 tables into one. Sorry, a bit novice over here.
      singulars
      1. POINSERT ERROR: Select isn't matching definition of columns
      1. USciw916
    3. COExecute this code and then try again: `DROP TABLE #ACSResults DROP TABLE #HRRep`
      singulars
      1. POINSERT ERROR: Select isn't matching definition of columns
      1. USPablo Claus
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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