Godwin Godwin - 1 year ago 98
SQL Question

Insert Into Select with Two Select Statement's from the same Table with Inner Select Condition

INSERT INTO Training
(
TrainingID
,EmployeeID
,TrainingEmployeeStatusID
,TrainingSessionID
,DepartmentID
,LastModified
,RevisionNum
)

SELECT
(SELECT TrainingID from TrainingGlobal.TrainingInformation
WHERE TrainingDesc = 'A First Value')
, FirmEmployeeID
, 5
, TrainingSessionID
, DepartmentID
, GETDATE()
, 1

UNION ALL SELECT
(SELECT TrainingID from TrainingGlobal.TrainingInformation
WHERE TrainingDesc = 'Another Value')
, FirmEmployeeID
, 5
, TrainingSessionID
, DepartmentID
, GETDATE()
, 1


From #ATempTable
WHERE DepartmentID IN ( SELECT DepartmentID from TrainingGlobal.DepartmentIDs)
AND FirmEmployeeID IN (SELECT EmployeeID from TrainingGlobal.EmployeeIDS)


My question is if it is possible to have two select statements on an Insert Into where the select statements are selecting from the same table, but changing the condition of an inner select on each new one.

As in the first line of each select statement would have a different condition for the WHERE clause, but the information is all coming from one temporary table.

I'm using MS-SQL 2016

Answer Source

A JOIN would seem to be simpler:

SELECT ti.TrainingInformation
      FirmEmployeeID,
      (CASE WHEN ti.TrainingDesc = 'A First Value' THEN 5 ELSE 6 END),
      TrainingSessionID, DepartmentID, GETDATE(), 1
FROM #ATempTable a CROSS JOIN
     (SELECT ti.*
      FROM Training.TrainingInformation ti
      WHERE TrainingDesc IN ('A First Value', 'Another Value')
     ) ti;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download