SQL2005 Insert getting 1 field from another table

Welcome to the forums!.
May the following example will help you:
G. Inserting data using the SELECT and EXECUTE options

The following example shows three different methods for getting data from one table and inserting it into another. Each is based on a multitable SELECT statement that includes an expression and a literal value in the column list.
The first INSERT statement uses a SELECT statement directly to retrieve data from the source tables (Employee, SalesPerson, and Contact) and store the result set in the EmployeeSales table. The second INSERT executes a stored procedure that contains the SELECT statement, and the third INSERT executes the SELECT statement as a literal string.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
DROP PROCEDURE uspGetEmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( DataSource varchar(20) NOT NULL,
EmployeeID varchar(11) NOT NULL,
LastName varchar(40) NOT NULL,
SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales
AS
SELECT 'PROCEDURE', e.EmployeeID, c.LastName,
sp.SalesYTD
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...SELECT example
INSERT dbo.EmployeeSales
SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT EmployeeSales
EXECUTE uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT EmployeeSales
EXECUTE
('
SELECT ''EXEC STRING'', e.EmployeeID, c.LastName,
sp.SalesYTD
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE ''2%''
ORDER BY e.EmployeeID, c.LastName
');
GO
--Show results.
SELECT DataSource,EmployeeID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

Looks like you are looking for something like this:

Code:

INSERT INTO Tablea
    (D,
    yID,
    CID,
    TC,
    TN,
    SpType,
    Alert,
    DateCreated,
    CreatedBy)
SELECT
    101393,
    CID,
    CID ,
    '120766',
    '(CRP)',
    'GEL',
    '',
    getdate(),
    'me'
FROM
    tableb tc
WHERE
    tc.CID=178973 AND
    TC.CD='ALPHA/COMBO';