CODEWITHSUNDEEP
sqlsql-server
TheBA.BI.BO
TheBA.BI.BO

Reputation: 49

How do Insert New Records only into a table with multiple Unions?

I have created a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ODSCustomerContact]') AND type in (N'U'))
DROP TABLE [ODSCustomerContact]
Go

Create Table ODSCustomerContact 
(
    CustomerContactInt int NOT NULL identity PRIMARY KEY,
    ContactID           varchar(20) NULL,
    SiteID              varchar(20) NULL,
    ClientName          nvarchar(max) NULL,
    ContactFirstName    nvarchar(max) NULL,
    ContactLastName     nvarchar(max) NULL,
    Position            nvarchar(max) NULL,
    Phone               nvarchar(max) NULL,
    MobilePhone         nvarchar(max) NULL,
    EmailAddress        nvarchar(max) NULL
)

I have inserted the Initial or First Batch of Data in this table using this script.

insert into SEC_ODS.dbo.ODSCustomerContact 
(ContactID,
SiteID,
ClientName,
ContactFirstName,
ContactLastName,
Position,
Phone,
MobilePhone,
EmailAddress)

   select 
       CONVERT(int, CAST(BusinessEntityID AS varchar(20)) + CAST(ContactID AS varchar(20))) as ContactID,
      'EXCL_E4SE' as SiteID,
      Clientname, 
      Contact_FirstName, 
      Contact_LastName ,
      Position,
      Phone,
      MobilePhone,
      EmailAdress
   from (  
       select distinct 
          e.BusinessEntityID,
          e.ContactID,
          d.SoldToShortName as Clientname, 
          e.FirstName as Contact_FirstName, 
          e.LastName as Contact_LastName ,
          e.Position,
          e.Phone,
          e.MobilePhone,
          e.EmailAddr as EmailAdress
       from 
          EXCL_E4SE.dbo.projectrule d
       left join 
          EXCL_E4SE.dbo.BusinessEntityAssociation BEA on d.soldtoID = BEA.customerID 
       left join 
          EXCL_E4SE.dbo.BusinessEntityContact e on e.BusinessEntityID = BEA.BusinessEntityID 
       where 
          d.SoldToShortName IS NOT NULL
          and e.BusinessEntityID is not null) a --order by Clientname

UNION ALL

select CONVERT(int,CAST(BusinessEntityID AS varchar(20)) + CAST(ContactID AS varchar(20))) as ContactID,
  'ASIA_E4SE' as SiteID,
  Clientname, 
  Contact_FirstName, 
  Contact_LastName ,
  Position,
  Phone,
  MobilePhone,
  EmailAdress
from (  
  select distinct e.BusinessEntityID,
    e.ContactID,
    d.SoldToShortName as Clientname, 
    e.FirstName as Contact_FirstName, 
    e.LastName as Contact_LastName ,
    e.Position,
    e.Phone,
    e.MobilePhone,
    e.EmailAddr as EmailAdress

  from ASIA_E4SE.dbo.projectrule d
  left join ASIA_E4SE.dbo.BusinessEntityAssociation BEA 
  on d.soldtoID = BEA.customerID 
  left join ASIA_E4SE.dbo.BusinessEntityContact e 
  on e.BusinessEntityID = BEA.BusinessEntityID 

  where d.SoldToShortName IS NOT NULL
     and e.BusinessEntityID is not null
     ) b --order by Clientname


UNION ALL


select CONVERT(int,CAST(BusinessEntityID AS varchar(20)) + CAST(ContactID AS varchar(20))) as ContactID,
  'BGD_E4SE' as SiteID,
  Clientname, 
  Contact_FirstName, 
  Contact_LastName ,
  Position,
  Phone,
  MobilePhone,
  EmailAdress
from (  
  select distinct e.BusinessEntityID,
    e.ContactID,
    d.SoldToShortName as Clientname, 
    e.FirstName as Contact_FirstName, 
    e.LastName as Contact_LastName ,
    e.Position,
    e.Phone,
    e.MobilePhone,
    e.EmailAddr as EmailAdress

  from BGD_E4SE.dbo.projectrule d
  left join BGD_E4SE.dbo.BusinessEntityAssociation BEA 
  on d.soldtoID = BEA.customerID 
  left join BGD_E4SE.dbo.BusinessEntityContact e 
  on e.BusinessEntityID = BEA.BusinessEntityID 

  where d.SoldToShortName IS NOT NULL
     and e.BusinessEntityID is not null
     ) c order by Clientname

The insert script above was successful. However my script below, which inserts NEW RECORDS ONLY does not work. The error that I get when I run this script is this.

Msg 1033, Level 15, State 1, Line 125
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

This is the Insert new records only script. Can someone help me correct this? Thank you

actually it is up to ALIAS letter M and not Alias letter C, I did this to avoid the long script inside the box. Rest Assured that ALIAS from letter D to Letter M are just as the same with ALIAS table A,B and C but with different database source such as EXCL_E4SE as A, ASIA_E4SE as B ....

insert into SEC_ODS.dbo.ODSCustomerContact 
(ContactID,
SiteID,
ClientName,
ContactFirstName,
ContactLastName,
Position,
Phone,
MobilePhone,
EmailAddress)

select 
x.ContactID,
x.SiteID,
x.ClientName,
x.ContactFirstName,
x.ContactLastName,
x.Position,
x.Phone,
x.MobilePhone,
x.EmailAddress
from
(
select CONVERT(int,CAST(BusinessEntityID AS varchar(20)) + CAST(ContactID AS varchar(20))) as ContactID,
  'EXCL_E4SE' as SiteID,
  Clientname, 
  Contact_FirstName, 
  Contact_LastName ,
  Position,
  Phone,
  MobilePhone,
  EmailAdress
from (  
  select distinct e.BusinessEntityID,
    e.ContactID,
    d.SoldToShortName as Clientname, 
    e.FirstName as Contact_FirstName, 
    e.LastName as Contact_LastName ,
    e.Position,
    e.Phone,
    e.MobilePhone,
    e.EmailAddr as EmailAdress

  from EXCL_E4SE.dbo.projectrule d
  left join EXCL_E4SE.dbo.BusinessEntityAssociation BEA 
  on d.soldtoID = BEA.customerID 
  left join EXCL_E4SE.dbo.BusinessEntityContact e 
  on e.BusinessEntityID = BEA.BusinessEntityID 

  where d.SoldToShortName IS NOT NULL
     and e.BusinessEntityID is not null
     ) a --order by Clientname

UNION ALL


select CONVERT(int,CAST(BusinessEntityID AS varchar(20)) + CAST(ContactID AS varchar(20))) as ContactID,
  'ASIA_E4SE' as SiteID,
  Clientname, 
  Contact_FirstName, 
  Contact_LastName ,
  Position,
  Phone,
  MobilePhone,
  EmailAdress
from (  
  select distinct e.BusinessEntityID,
    e.ContactID,
    d.SoldToShortName as Clientname, 
    e.FirstName as Contact_FirstName, 
    e.LastName as Contact_LastName ,
    e.Position,
    e.Phone,
    e.MobilePhone,
    e.EmailAddr as EmailAdress

  from ASIA_E4SE.dbo.projectrule d
  left join ASIA_E4SE.dbo.BusinessEntityAssociation BEA 
  on d.soldtoID = BEA.customerID 
  left join ASIA_E4SE.dbo.BusinessEntityContact e 
  on e.BusinessEntityID = BEA.BusinessEntityID 

  where d.SoldToShortName IS NOT NULL
     and e.BusinessEntityID is not null
     ) b --order by Clientname


UNION ALL


select CONVERT(int,CAST(BusinessEntityID AS varchar(20)) + CAST(ContactID AS varchar(20))) as ContactID,
  'BGD_E4SE' as SiteID,
  Clientname, 
  Contact_FirstName, 
  Contact_LastName ,
  Position,
  Phone,
  MobilePhone,
  EmailAdress
from (  
  select distinct e.BusinessEntityID,
    e.ContactID,
    d.SoldToShortName as Clientname, 
    e.FirstName as Contact_FirstName, 
    e.LastName as Contact_LastName ,
    e.Position,
    e.Phone,
    e.MobilePhone,
    e.EmailAddr as EmailAdress

  from BGD_E4SE.dbo.projectrule d
  left join BGD_E4SE.dbo.BusinessEntityAssociation BEA 
  on d.soldtoID = BEA.customerID 
  left join BGD_E4SE.dbo.BusinessEntityContact e 
  on e.BusinessEntityID = BEA.BusinessEntityID 

  where d.SoldToShortName IS NOT NULL
     and e.BusinessEntityID is not null
     ) c order by Clientname, ClientID

     )  x

     where not exists (select * from SEC_ODS.dbo.ODSCustomerContact)

Upvotes: 0

Views: 80

Answers (2)

Alberto Solano
Alberto Solano

Reputation: 8227

Your query with INSERT/SELECT/UNION ALL is not working because the ORDER BY clause is allowed only at the end of the instruction and in the outer query, as stated in the documentation:

In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY is allowed only at the end of the statement. This restriction applies only to when you specify UNION, EXCEPT and INTERSECT in a top-level query and not in a subquery.

You have to remove your ORDER BY clauses from your query to make this script working correctly.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166366

As you did with the other ORDER BYs 

where d.SoldToShortName IS NOT NULL
 and e.BusinessEntityID is not null
 ) a --order by Clientname
....

 where d.SoldToShortName IS NOT NULL
 and e.BusinessEntityID is not null
 ) b --order by Clientname

you also need to comment out the following

where d.SoldToShortName IS NOT NULL
 and e.BusinessEntityID is not null
 ) c -->order by Clientname, ClientID<--

 )  x

Upvotes: 1

Related Questions