Kalyani Sulkar
asked on
Column name or number of supplied values does not match table definition
Hello,
I have been playing around with this error for a while now and not getting anywhere. I do feel my joins are correct. Can someone point me in right direction? Thank you in advance!
IF OBJECT_ID ('tempdb..#Temp1') IS NOT NULL
BEGIN
DROP TABLE #Temp1
END
DECLARE @StartDate DATETIME
SET @StartDate = '2010-01-01 00:00:00.000'
CREATE TABLE #Temp1
( BookingID INT PRIMARY KEY
,BookingNumber VARCHAR (20)
,StartDate DATETIME
,EndDate DATETIME
,OptionInDate DATETIME
,OptionOutDate DATETIME
,BookedServiceID INT
,ServiceID INT
)
INSERT INTO #Temp1
SELECT DISTINCT
B.BookingID
,B.BookingNumber
,B.StartDate
,B.EndDate
,BO.OptionInDate
,BO.OptionOutDate
,BS.BookedServiceID
,BS.ServiceID
FROM dbo.Booking b
INNER JOIN DBO.SERVICE BS ON BS.BOOKINGID = B.BOOKINGID
INNER JOIN DBO.OPTION BO ON BO.BOOKEDSERVICEID = BS.BOOKEDSERVICEID
WHERE b.StartDate >= @StartDate
AND BO.OptionInDate >= @StartDate
--ORDER BY B.BookingID
SELECT * FROM #Temp
I have been playing around with this error for a while now and not getting anywhere. I do feel my joins are correct. Can someone point me in right direction? Thank you in advance!
IF OBJECT_ID ('tempdb..#Temp1') IS NOT NULL
BEGIN
DROP TABLE #Temp1
END
DECLARE @StartDate DATETIME
SET @StartDate = '2010-01-01 00:00:00.000'
CREATE TABLE #Temp1
( BookingID INT PRIMARY KEY
,BookingNumber VARCHAR (20)
,StartDate DATETIME
,EndDate DATETIME
,OptionInDate DATETIME
,OptionOutDate DATETIME
,BookedServiceID INT
,ServiceID INT
)
INSERT INTO #Temp1
SELECT DISTINCT
B.BookingID
,B.BookingNumber
,B.StartDate
,B.EndDate
,BO.OptionInDate
,BO.OptionOutDate
,BS.BookedServiceID
,BS.ServiceID
FROM dbo.Booking b
INNER JOIN DBO.SERVICE BS ON BS.BOOKINGID = B.BOOKINGID
INNER JOIN DBO.OPTION BO ON BO.BOOKEDSERVICEID = BS.BOOKEDSERVICEID
WHERE b.StartDate >= @StartDate
AND BO.OptionInDate >= @StartDate
--ORDER BY B.BookingID
SELECT * FROM #Temp
comment out
and run it... do you get list of inserted rows?
--INSERT INTO #Temp1and run it... do you get list of inserted rows?
and use this :)
SELECT * FROM #Temp
>>>
SELECT * FROM #Temp
>>>
SELECT * FROM #Temp1ASKER
HainKurt - As I am creating table, there is nothing in there. How does that help?
first I said comment out insert line only to see the records that will be inserted...
second, you are trying to select from #Temp not #Temp1, which does not exists
second, you are trying to select from #Temp not #Temp1, which does not exists
ASKER
I missed that when I changed the table and column names for privacy reasons. The temp name is correct in all places. Also, if the table name was wrong I would have gotten a message saying "can't find table xyz or that it doesn't exist...." that's not the error I am getting.
I cannot reproduce the error
so, what is your select statement returns?
SELECT DISTINCT...
IF OBJECT_ID ('tempdb..#Temp1') IS NOT NULL
BEGIN
DROP TABLE #Temp1
END
DECLARE @StartDate DATETIME
SET @StartDate = '2010-01-01 00:00:00.000'
CREATE TABLE #Temp1
( BookingID INT PRIMARY KEY
,BookingNumber VARCHAR (20)
,StartDate DATETIME
,EndDate DATETIME
,OptionInDate DATETIME
,OptionOutDate DATETIME
,BookedServiceID INT
,ServiceID INT
)
INSERT INTO #Temp1
SELECT DISTINCT 1, 'XXX', getdate(), getdate(), getdate(), getdate(), 0, 5
SELECT * FROM #Temp1
BookingID BookingNumber StartDate EndDate OptionInDate OptionOutDate BookedServiceID ServiceID
1 XXX 2017-07-19 17:35:45.513 2017-07-19 17:35:45.513 2017-07-19 17:35:45.513 2017-07-19 17:35:45.513 0 5so, what is your select statement returns?
SELECT DISTINCT...
ASKER
I keep getting the same error. Maybe there is something related to the data I am using. I will take a closer look.
Thanks!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Figured it out
ASKER