wayne7215 wayne7215 - 24 days ago 4
SQL Question

SQL Server Insert "The multi-part identifier could not be bound"

I'm coming from Oracle and have a few problems with SQL Server Management Studio.

We have a View but it's horrible slow and takes around 50 seconds. So I would like to create a static table and insert the result of the view into this table by a trigger or with a daily batch job.

The view looks like this:

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;


I've created a table
dbo.ser_number_all
and added a uniqueidentifier column to have a primary key, which we don't have in the view above.

In Oracle I would do an insert now like this:

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);


But SQL Server always returns an error


The multi-part identifier init_id_seq.nextval could not be bound


and the same on all the columns in the

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"`.


What am I doing wrong here?

Thnx

Answer

Try this

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);

OR

    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);
Comments