合并导致转换错误日期时间 --> INT

Question

我不知道为什么会出现这个错误。还有谁能帮我指出这个错误吗?我试图合并到一个临时表，并得到错误

不允许从数据类型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)
;

Answer 1

基于猜测ProviderBibDateAdded类型为整数，并且您正在尝试将日期时间塞进其中，尝试将更新语句更改为:

UPDATE
    SET ProviderCustNum      = pt.ProviderCustNum,
        ProviderDivID        = pt.ProviderDivID,
        ProviderBibDateAdded = Cast(pt.ProviderBibDateAdded as Integer),
        ProviderQtyOwned     = pt.ProviderQtyOwned

和你的insert语句:

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, 
        cast(pt.ProviderBibDateAdded as Integer), 
        pt.ProviderQtyOwned)

如果这确实是问题所在，那么您可以在插入任何内容之前创建具有显式数据类型的临时表作为开始。

Declare @TitleData (
     KTSID integer not null,
     FLRID integer not null,
     SiteShelfID integer not null,
     SiteCustNum varChar(??),
     SiteDivID integer not null,
     SiteBibDateAdded Datetime not null,
     SiteQtyOwned smallInt,
     ProviderShelfID integer not null,
     ProviderCustNum varChar(??) Null,
     ProviderDivID Integer null,
     ProviderBibDateAdded Datetime null,
     ProviderQtyOwned null)
Insert @TitleData (KTSID, FLRID, SiteShelfID, 
    SiteCustNum, SiteDivID, SiteBibDateAdded,
    SiteQtyOwned, ProviderShelfID)
SELECT BookID, ResourceID, ls.ID,
   ls.CustomerNumber, ls.DivisionID,
   st.DateCreated, st.TotalCopies,
   ls.ParentLibraryShelfID
From dapadmin.ShelfTitle st
    JOIN dapadmin.LibraryShelf ls 
       ON st.LibraryShelfID = ls.ID
    JOIN #TitlesToInclude tti     
       ON st.ID = tti.ShelfTitleId