First set up some sample data (it would help in the future if you provide a populated temp table for sample data):

/* THIS IS JUST FOR GENERATING SAMPLE SOURCE DATA */

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL

DROP TABLE #TestData

;WITH sampledata AS

(

SELECT * FROM

(VALUES

(1,0.15,1,9),

(1,0.05,2,8),

(2,0.05,1,7),

(3,0.05,1,7),

(3,0.10,2,9),

(3,0.25,3,6)

) DATA (ValueA,ValueB,ValueC,ValueX))

SELECT

IDENTITY(INT,1,1) AS ID

,ValueA

,ValueB

,ValueC

,ValueX

INTO

#TestData

FROM

sampledata

SELECT * FROM #TestData

/* END SOURCE DATA */

Now to do what you want in a single statement without using dynamic SQL I had to let ValueC ride the coattails of the first insert so I needed to add ValueC to TableA. You need to get the value into TableB somehow and this was the easiest method I could think of. The other option would just be two individual insert statements.

IF OBJECT_ID('tempdb..#TestTableAlpha') IS NOT NULL

DROP TABLE #TestTableAlpha

IF OBJECT_ID('tempdb..#TestTableBravo') IS NOT NULL

DROP TABLE #TestTableBravo

CREATE TABLE #TestTableAlpha (

[ID] INT IDENTITY(100,1) NOT NULL, --100 so we can see the result easier

[ColA] INT NULL,

[ColB] DECIMAL(5,2) NULL,

[ColC] INT NULL,

[ColX] INT NULL,

PRIMARY KEY (ID))

CREATE TABLE #TestTableBravo (

[ID] INT IDENTITY(1,1) NOT NULL,

[ColA] INT NULL,

[ColB] DECIMAL(5,2) NULL,

[ColC] INT NULL,

PRIMARY KEY (ID))

INSERT INTO #TestTableAlpha

(ColA,ColB,ColC,ColX)

OUTPUT

INSERTED.ID AS ColA,

INSERTED.ColB,

INSERTED.ColC

INTO #TestTableBravo

SELECT

ValueA,

ValueB,

ValueC,

ValueX

FROM #TestData

SELECT * FROM #TestTableAlpha

SELECT * FROM #TestTableBravo