Я не могу понять, почему эта ошибка возникает. Может ли кто-нибудь еще помочь мне найти ошибку? Я пытаюсь объединиться с временной таблицей и получаю сообщение об ошибке
Неявное преобразование из типа данных datetime в int не допускается. Используйте функцию CONVERT для выполнения этого запроса. Ошибка идентифицируется как возникающая в строке, где начинается MERGE.
Запрос:
SELECT
BookID AS KTSID,
ResourceID AS FLRID,
ls.ID AS SiteShelfID,
ls.CustomerNumber AS SiteCustNum,
ls.DivisionID AS SiteDivID,
st.DateCreated AS SiteBibDateAdded,
st.TotalCopies AS SiteQtyOwned,
ls.ParentLibraryShelfID AS ProviderShelfID,
NULL AS ProviderCustNum,
NULL AS ProviderDivID,
NULL AS ProviderBibDateAdded,
NULL AS ProviderQtyOwned
INTO #TitleData
FROM dapadmin.ShelfTitle st
JOIN dapadmin.LibraryShelf ls ON st.LibraryShelfID = ls.ID
JOIN #TitlesToInclude tti ON st.ID = tti.ShelfTitleId
MERGE #TitleData WITH(HOLDLOCK) AS td
USING (SELECT
st.BookID AS KTSID,
st.ResourceID AS FLRID,
st.LibraryShelfID AS SiteShelfID,
ls2.CustomerNumber AS SiteCustNum,
ls2.DivisionID AS SiteDivID,
s.ProviderShelfID,
ls.CustomerNumber AS ProviderCustNum,
ls.DivisionID AS ProviderDivID,
st.DateCreated AS ProviderBibDateAdded,
st.TotalCopies AS ProviderQtyOwned
FROM @ShelfIdsToSearch s
JOIN dapadmin.ShelfTitle st
ON s.ProviderShelfId = st.LibraryShelfID
JOIN #TitleData td
ON td.KTSID = st.BookID
AND td.FLRID = st.ResourceID
AND td.SiteShelfId = s.LibraryShelfId
AND td.ProviderShelfID = st.LibraryShelfID
JOIN #TitlesToInclude tti
ON st.ID = tti.ShelfTitleId
OR s.ProviderHasChanged = 1
JOIN dapadmin.LibraryShelf ls
ON s.ProviderShelfId = ls.ID
JOIN dapadmin.LibraryShelf ls2
ON s.LibraryShelfId = ls.ID
) AS pt
ON td.KTSID = pt.KTSID
AND td.FLRID = pt.FLRID
AND pt.SiteShelfID = td.SiteShelfID
WHEN MATCHED THEN
UPDATE
SET ProviderCustNum = pt.ProviderCustNum,
ProviderDivID = pt.ProviderDivID,
ProviderBibDateAdded = pt.ProviderBibDateAdded,
ProviderQtyOwned = pt.ProviderQtyOwned
WHEN NOT MATCHED THEN
INSERT (KTSID, FLRID, SiteShelfID, SiteCustNum, SiteDivID,
SiteBibDateAdded, SiteQtyOwned, ProviderShelfID,
ProviderCustNum, ProviderDivID,
ProviderBibDateAdded, ProviderQtyOwned)
VALUES (pt.KTSID, pt.FLRID, pt.SiteShelfID, pt.SiteCustNum, pt.SiteDivID,
NULL, NULL, pt.ProviderShelfID,
pt.ProviderCustNum, pt.ProviderDivID,
pt.ProviderBibDateAdded, pt.ProviderQtyOwned)
;