Database

Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a2 ON a2.Name = b. AnotherTableAName
UNION
Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a ON a.Name = b. TableAName

--Insert into #TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM #TableB b
INNER JOIN #TableA a ON a.Name = b.TableAName  OR a.Name = b.AnotherTableAName

DECLARE @DOB DATETIME = GETDATE()
DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIME

SELECT @ODate = CONVERT(DATE,@DOB)
SET @OpenDT = @ODate
SELECT @OpenDT = DATEADD(HOUR,@OHour,@OpenDT)
SELECT @OpenDT = DATEADD(MINUTE,@OMin,@OpenDT)

PRINT @OpenDT

DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIME
declare @DOB DATE = getdate()  --Or whatever date drom the database
SELECT @OpenDT = @DOB

SELECT @OpenDT = DATEADD(MINUTE,(@OHour * 60) + @OMin, @OpenDT)

PRINT @OpenDT

DECLARE @TabAId int;
INSERT INTO TabA (CurrentDate) VALUES (GetUtcDate());
SET @TabAId = Scope_Identity();

INSERT INTO TabC (Col1, Col2, TabAId)
SELECT Col1, Col2, @TabAId FROM TabB;

DECLARE @MyTableVar table (DocumentListId int, [Name] varchar(max), [Description] varchar(max));
INSERT INTO dbo.DocumentList (CreatedDate,CreatedBy,ModifiedDate,ModifiedBy)
  OUTPUT INSERTED.DocumentListId, 
         htge.[Name], 
         htge.[Description]
  INTO @MyTableVar
SELECT  GETDATE() ,CURRENT_USER,GETDATE(),CURRENT_USER, htge.[Name], htge.[Description]
FROM    OPENROWSET('SQLNCLI', 'Server=xxx;UID=xx;PWD=xxx',
        'SELECT * FROM WEB_WebAdmin.dbo.HealthTopicGroup where HealthTopicGroupId < 45') as htge;

SELECT * frOM @MyTableVar