Temp Table Values

Hello,

I have created a temporary table that has an identity and default values that it creates and sets but also it needs to get values from a sub query for the final insert into the temp table. I have my create temp table, and my insert into set up but my select is confusing me since some of the values I need to select are determined as they are entered into the temp table. Is there a way to alias them or not put them in the select? If I leave them out it says there are more values in the insert than in the select.


Create Table #TempLabTestConfigImport

(temp_id int identity(1,1),system_id int default 2250, labtestkey nvarchar(10) null,

valuecode nvarchar(10)null, value_description nvarchar(50) null,

value_code_system varchar(10) default 'L',value_type char(2) null,

units nvarchar(5) null,sequence_number int null, table_name nvarchar(50) null,

field_name nvarchar(50) null,created_by int default 57,create_timestamp datetime default getdate(),

modified_by int default 57,modify_timestamp datetime default getdate(),

row_timestamp datetime default getdate())

/*CTE and sub query that combines the data for import and inserts it into temp table*/

With labtestcheck (labtestcode)

as

(

select a.labtestcode

from EMR_temp_labtest_configupdate a

Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)

)

Insert Into #TempLabTestConfigImport

(temp_id,system_id,labtestkey,valuecode,value_description,value_code_system,

value_type,units,sequence_number,table_name,field_name,created_by,create_timestamp,modified_by,

modify_timestamp, row_timestamp)

SELECT temp_id,system_id,

l.labtestcode,t.valuecode,t.valuedesc,value_code_system,

t.valuetype,t.units,t.SequenceCount,t.tablename,t.fieldname, created_by,

create_timestamp,modified_by,modify_timestamp, row_timestamp

FROM labtestcheck l

INNER JOIN

(

select t.labtestcode,b.valuecode,b.valuedesc,b.valuetype,b.units,

row_number() over (partition by t.labtestcode order by b.valuecode) as SequenceCount,

b.tablename,b.fieldname

from EMR_temp_labtest_configupdate a

inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode

inner join labtestcheck t on t.labtestcode = a.labtestcode)t

ON t.labtestcode=l.labtestcode