Insert error 3

[jeffsturgeon2002]

I want to add in the following fields (along with many more for that matter)

ISNULL(pp.[Phone1],'')AS Phone1,
ISNULL(pp.[Phone1Type],'')AS Phone1Type,
ISNULL(pp.[Phone2],'')AS Phone2,
ISNULL(pp.[Phone2Type],'')AS Phone2Type,

to this query. I can't seem to get these fields in without QA erroring on me, telling me "Insert Error: Column name or number of supplied values does not match table definition."

/*Appointment Times report */

SET NOCOUNT ON

DECLARE @Today DATETIME
DECLARE @Tomorrow DATETIME
SET @Today = '07/14/2006'
SET @Tomorrow = dateadd(d, 1, '07/14/2006')

DECLARE @tblTemp TABLE
(
			Resource VARCHAR(60),
			Start DATETIME,
			Stop DATETIME,
			Patient VARCHAR(256),
-- 			Phone1 VARCHAR(15),
-- 			Phone1Type VARCHAR(25),
-- 			Phone2 VARCHAR(15),
-- 			Phone2Type VARCHAR(25),
			[Column] VARCHAR(64),
			Facility VARCHAR(60),
			Allocations VARCHAR(4096),
			DateOnly VARCHAR(15)
) 

INSERT INTO @tblTemp

SELECT 	d.Listname, Start, Stop, ' ' AS Patient,CAST(aps.ListOrder AS Varchar(10)) AS [Column], f.ListName as Facility,
	dbo.sfnGetAllocsForSlot(aps.ApptSlotId) AS Allocations, CONVERT(VARCHAR(15), Start, 101)

FROM	ApptSlot aps

	JOIN 	Schedule s ON aps.ScheduleId = s.ScheduleId
	JOIN	DoctorFacility df ON s.DoctorResourceId = df.DoctorFacilityId
	JOIN	DoctorFacility f ON aps.FacilityId = f.DoctorFacilityId
	JOIN 	DoctorFacility d ON s.DoctorResourceId = d.DoctorFacilityId

WHERE	
		--Filter on resource
		(
		(NULL IS NOT NULL AND df.DoctorFacilityId IN (NULL)) OR
		(NULL IS NULL)
		)
		AND
		(
		(NULL IS NOT NULL AND aps.FacilityId IN (NULL)) OR
		(NULL IS NULL)
		)
		AND (Start >= @Today OR @Today IS NULL)
		AND (Start < @Tomorrow OR @Tomorrow IS NULL)
		AND ApptId IS NULL
		AND APS.ListOrder <> -1

INSERT INTO @tblTemp

SELECT  DISTINCT    	d.ListName, 
			ApptStart, 
			ApptStop,  -- need distinct because ApptSlot can have more than row per appt
			CASE WHEN a.ApptKind = 1 THEN pp.Last + ', ' + pp.First
			 WHEN a.ApptKind = 2 THEN 'Doctor Appt'
			 WHEN a.ApptKind = 3 THEN 'Resource Appt'
			 WHEN a.ApptKind = 5 THEN 'Block Out Appt'
			 ELSE '' END AS Patient,
-- 			ISNULL(pp.[Phone1],'')AS Phone1,
-- 			ISNULL(pp.[Phone1Type],'')AS Phone1Type,
-- 			ISNULL(pp.[Phone2],'')AS Phone2,
-- 			ISNULL(pp.[Phone2Type],'')AS Phone2Type,
			CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column],
			f.ListName AS Facility,
			ISNULL(apt.Name,'<No Appointment Type>')  + ' - ' + CONVERT(varchar(255), ISNULL(a.Notes,''))  AS Allocations,
			CONVERT(VARCHAR(15), ApptStart, 101) AS DateOnly

FROM	Appointments a

	JOIN DoctorFacility f ON a.FacilityId = f.DoctorFacilityId
	LEFT JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
	LEFT JOIN ApptSlot aps ON a.AppointmentsId = aps.ApptId
	JOIN DoctorFacility d ON a.ResourceId = d.DoctorFacilityId
	LEFT JOIN ApptType apt ON a.ApptTypeId = apt.ApptTypeId

WHERE   
		(
		(NULL IS NOT NULL AND a.ResourceId IN (NULL)) OR
		(NULL IS NULL)
		)
		AND
		(
		(NULL IS NOT NULL AND a.FacilityId IN (NULL)) OR
		(NULL IS NULL)
		)
		AND (ApptStart >= @Today OR @Today IS NULL)
		AND (ApptStop < @Tomorrow OR @Tomorrow IS NULL)
		AND ((ApptKind = 1 AND isnull(Canceled,0) = 0) OR (ApptKind <> 1))

SELECT *, Convert(VARCHAR(30), Stop, 101) AS DateOnlyAsText FROM @tblTemp
ORDER BY Resource, Facility, DateOnly, Start, [Column]

Jeff

SELECT * FROM users WHERE clue > 0

 

[ptheriault]

Jeff,
You only have 9 values listed in your select statement. When you don't list the fields in you insert you must select all the fields in the same order as you table is defined.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

At first glance, I think your number of columns selected and number to insert to are not matching up. You should use a column list for your insert. Like this:

insert into @zeTemp (col1, col2, col3)
select col1, col2, col3 from zeTable

This is an oversimplified example of course, but I think it will make it easier for you to ensure that columns in your insert match up.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

you also need to add them to your first select statement

INSERT INTO @tblTemp

SELECT     d.Listname, Start, Stop, ' ' AS Patient,
'','','','', --add this



CAST(aps.ListOrder AS Varchar(10)) AS [Column], f.ListName as Facility,
    dbo.sfnGetAllocsForSlot(aps.ApptSlotId) AS Allocations, CONVERT(VARCHAR(15), Start, 101)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

Jeff,
I miss counted but I agree with Alex and I would write it the way Alex has. I think it is easier to read that way.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jeffsturgeon2002]

ptheriault, Alex and SQLDenis,

Many many thanks .... you guys never steer me wrong. Thanks for breaking it down and explaining it so well to. Its one thing to just get an answer .... its another when you take the time and explain it.

Jeff

SELECT * FROM users WHERE clue > 0