I want to get rid of using one table by adding code as a subselect to the UNION-CodePudding

I have this working Union

INSERT INTO [Transform].PosSalesUnion 
         (DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber, poolID)


SELECT poolID, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber 
FROM [Transform].Devices_DeviceGroups_PosSales
  
UNION

SELECT NULL, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber 
FROM [Transform].PosSalesNotMatch

As you can see I read from table [Transform].PosSalesNotMatch. Now I hope to minimize the usage of separate tables and instead add the code that populate this table [Transform].PosSalesNotMatch.

The code that populate this table [Transform].PosSalesNotMatch is this code

INSERT INTO [Transform].PosSalesNotMatch
   SELECT a.DeviceId, a.DayClosureId, a.TransactionId, a.IndetId, a.TicketId,a.CategoryId,a.ArticleId,a.CurrencyId,a.Datetime,a.IsSuccessful,a.Quantity,a.Turnover,a.TurnoverTarget,a.SalesTransactionTypeId,a.ValidFrom,a.ValidTo,a.SerialNumber
   from [Transform].PosSales a
   left join [Transform].Devices_DeviceGroups b on a.DeviceId = b.DeviceId
   where b.DeviceId is null

Now I have tried to add this code as a subselect to the union but I get syntax error. The final code that I have tried is this

 INSERT INTO [Transform].PosSalesUnion 
         (DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber, poolID)
 

 SELECT poolID, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber 
 FROM [Transform].Devices_DeviceGroups_PosSales

   UNION

 SELECT NULL, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber 

   (SELECT a.DeviceId, a.DayClosureId, a.TransactionId, a.IndetId, a.TicketId,a.CategoryId,a.ArticleId,a.CurrencyId,a.Datetime,a.IsSuccessful,a.Quantity,a.Turnover,a.TurnoverTarget,a.SalesTransactionTypeId,a.ValidFrom,a.ValidTo,a.SerialNumber
   from [Transform].PosSales a
 left join [Transform].Devices_DeviceGroups b on a.DeviceId = b.DeviceId
   where b.DeviceId is null)

CodePudding user response：

Looks like the problem is you did not have the FROM keyword. On SQL Server you also have to give an alias to sub-queries.

 INSERT INTO [Transform].PosSalesUnion 
         (DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber, poolID)
 

   SELECT poolID, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber 
   FROM [Transform].Devices_DeviceGroups_PosSales

     UNION

   SELECT NULL, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber 
   FROM 
     (SELECT a.DeviceId, a.DayClosureId, a.TransactionId, a.IndetId, a.TicketId,a.CategoryId,a.ArticleId,a.CurrencyId,a.Datetime,a.IsSuccessful,a.Quantity,a.Turnover,a.TurnoverTarget,a.SalesTransactionTypeId,a.ValidFrom,a.ValidTo,a.SerialNumber
      from [Transform].PosSales a
      left join [Transform].Devices_DeviceGroups b on a.DeviceId = b.DeviceId
      where b.DeviceId is null
     ) as X

After looking at it formatted well I can see you don't need the sub-query

 INSERT INTO [Transform].PosSalesUnion 
         (DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber, poolID)
 

   SELECT poolID, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber 
   FROM [Transform].Devices_DeviceGroups_PosSales

     UNION

   SELECT NULL, a.DeviceId, a.DayClosureId, a.TransactionId, a.IndetId, a.TicketId,a.CategoryId,a.ArticleId,a.CurrencyId,a.Datetime,a.IsSuccessful,a.Quantity,a.Turnover,a.TurnoverTarget,a.SalesTransactionTypeId,a.ValidFrom,a.ValidTo,a.SerialNumber
   FROM [Transform].PosSales a
   LEFT JOIN [Transform].Devices_DeviceGroups b on a.DeviceId = b.DeviceId
   WHERE b.DeviceId is null