Server插入“无法绑定多部分标识符”

Question

我来自Oracle，在方面有一些问题。

我们有一个视图，但它是可怕的慢和大约50秒。因此，我希望创建一个静态表，并通过触发器或每天批处理作业将视图的结果插入到该表中。

视图如下所示：

CREATE VIEW [dbo].[Hierachical_View_with_Jobnumbers]
AS
    WITH ProductList AS
    (
        SELECT  
            p.Serialnumber, p.Type_Id,
            p.Date, p.Parent_Serialnumber, 
            p.ActiveJob_Jobnumber as JobNumber,
            N'/' + CONVERT(NVARCHAR(4000), ROW_NUMBER() OVER (ORDER BY p.Serialnumber)) + N'/' AS ProductNode_AsChar
        FROM 
            Products AS p
        WHERE 
            p.Parent_Serialnumber IS NULL

        UNION ALL

        SELECT  
            p.Serialnumber, p.Type_Id,
            p.Date, p.Parent_Serialnumber, 
            JobNumber,
            pl.ProductNode_AsChar + CONVERT(NVARCHAR(4000), ROW_NUMBER() OVER (ORDER BY p.Serialnumber)) + N'/'
        FROM 
            Products AS p
        INNER JOIN 
            ProductList AS pl ON p.Parent_Serialnumber = pl.Serialnumber
    ) 
    SELECT  
        Serialnumber, pt.Name as TypeName,
        Date, Parent_Serialnumber,
        JobNumber,
        CONVERT(HIERARCHYID, ProductNode_AsChar) AS ProductNode
    FROM 
        ProductList AS pl
    INNER JOIN 
        ProductTypes AS pt ON pl.Type_Id = pt.Id;

我创建了一个表dbo.ser_number_all，并添加了一个unique标识符列，以获得一个主键，这在上面的视图中是没有的。

在Oracle中，我现在会执行如下插入：

insert into [dbo].[ser_number_all]
  ( ID
  , Serialnumber
  , TypeName
  , Date
  , Parent_Serialnumber
  , JobNumber
  , ProductNode)
  select init_id_seq.nextval
          , Serialnumber
          , TypeName
          , Date
          , Parent_Serialnumber
          , JobNumber
          , ProductNode
  from dbo.Hierachical_View_with_Jobnumbers
where not exists (select 1
                  from Hierachical_View_with_Jobnumbers as hv
                  where hv. Serialnumber = ser_number_all.Serialnumber
                  and hv. TypeName = ser_number_all.TypeName
                  and hv. Date = ser_number_all.Date
                  and hv. Parent_Serialnumber = ser_number_all.Parent_Serialnumber
                  and hv. JobNumber = ser_number_all.JobNumber
                  and hv. ProductNode = ser_number_all.ProductNode);

但是Server总是返回一个错误

无法绑定多部分标识符init_id_seq.nextval。

中的所有列都是相同的。

select 1 statement. "ser_number_all.Serialnumber", "ser_number_all.TypeName", "ser_number_all.Date", "ser_number_all.Parent_Serialnumber", "ser_number_all.JobNumber" and "ser_number_all.ProductNode"`.

我在这里做错什么了？

丁烯

Answer 1

尝尝这个

insert into [dbo].[ser_number_all]
  ( ID
  , Serialnumber
  , TypeName
  , Date
  , Parent_Serialnumber
  , JobNumber
  , ProductNode)
  select init_id_seq.nextval
      , Serialnumber
      , TypeName
      , Date
      , Parent_Serialnumber
      , JobNumber
      , ProductNode
  from dbo.Hierachical_View_with_Jobnumbers as ser_number_all
where not exists (select 1
                  from Hierachical_View_with_Jobnumbers as hv
              where hv. Serialnumber = ser_number_all.Serialnumber
              and hv. TypeName = ser_number_all.TypeName
              and hv. Date = ser_number_all.Date
              and hv. Parent_Serialnumber = ser_number_all.Parent_Serialnumber
              and hv. JobNumber = ser_number_all.JobNumber
              and hv. ProductNode = ser_number_all.ProductNode);

或

    insert into [dbo].[ser_number_all]
  ( ID
  , Serialnumber
  , TypeName
  , Date
  , Parent_Serialnumber
  , JobNumber
  , ProductNode)
  select init_id_seq.nextval
      , Serialnumber
      , TypeName
      , Date
      , Parent_Serialnumber
      , JobNumber
      , ProductNode
  from dbo.Hierachical_View_with_Jobnumbers 
where not exists (select 1
                  from Hierachical_View_with_Jobnumbers as hv
              where hv. Serialnumber = Serialnumber
              and hv. TypeName = TypeName
              and hv. Date = Date
              and hv. Parent_Serialnumber = Parent_Serialnumber
              and hv. JobNumber = JobNumber
              and hv. ProductNode = ProductNode);

Answer 2

类似于下面的别名Hierachical_View_with_Jobnumbers到hv，并检查它的值是否不在ser_number_all中

    insert into [dbo].[ser_number_all]
  ( ID
   , Serialnumber
   , TypeName
   , Date
   , Parent_Serialnumber
  , JobNumber
  , ProductNode)
  select hv.nextval
          , hv.Serialnumber
          , hv.TypeName
          , hv.[Date]
          , hv.Parent_Serialnumber
          , hv.JobNumber
          , hv.ProductNode
    from dbo.Hierachical_View_with_Jobnumbers hv
  where not exists (select 1
                                  from[ser_number_all]  ser_number_all
                                 where hv. Serialnumber = ser_number_all.Serialnumber
                                     and hv. TypeName = ser_number_all.TypeName
                                     and hv. Date = ser_number_all.Date
                                     and hv. Parent_Serialnumber = ser_number_all.Parent_Serialnumber
                                     and hv. JobNumber = ser_number_all.JobNumber
                                     and hv. ProductNode = ser_number_all.ProductNode);

Answer 3

尝尝这个

insert into [dbo].[ser_number_all]
  ( ID
  , Serialnumber
  , TypeName
  , Date
  , Parent_Serialnumber
  , JobNumber
  , ProductNode)
  select init_id_seq.nextval
      , Serialnumber
      , TypeName
      , Date
      , Parent_Serialnumber
      , JobNumber
      , ProductNode
  from dbo.Hierachical_View_with_Jobnumbers as ser_number_all
where not exists (select 1
                  from Hierachical_View_with_Jobnumbers as hv
              where hv. Serialnumber = ser_number_all.Serialnumber
              and hv. TypeName = ser_number_all.TypeName
              and hv. Date = ser_number_all.Date
              and hv. Parent_Serialnumber = ser_number_all.Parent_Serialnumber
              and hv. JobNumber = ser_number_all.JobNumber
              and hv. ProductNode = ser_number_all.ProductNode);