Create Identity field for temp table

What I need to do is create a identity field for another table and insert data into it to push to another table.

First I have to get the start of the identity number from  an Oracle table, then I need to make that the start of the identiy number for my temp table and auto incriment it when I insert my records into it, then I have to push this table up to and oracle db.  

What I need help on is I am creating and picking the number, but I need to know how to assign it as my identity field and then insert records.

Create Proc dbo.UpdateCognos

AS

--Declare Variables
Declare @SidTemp varchar(16)

	--CREATE A TEMP TABLE TO INSERT THE TEMP SID
	Create Table #SidTemp (SidTempId varchar(16))

	--Get the Temp Sid from  
	Select @SidTemp = KPISID from OpenQuery(METRICS, 'select max(KPI_SID) + 1 as KPISID from (select KPI_SID from METRIC_STORE.kpi 
									  union 
									  select KPI_USER_VALUE_SID from METRIC_STORE.KPI_USER_VALUE)')

	--Insert temp Sid into tempTable
	Insert #SidTemp  (SidTempID) VALUES (@SidTemp)


	--Get data to insert into the Temp Table
	Select	KPISID,
			TimePeriodSid,
			1,
			ActualVal,
			TargetVal,
			ToleranceVal,
			ToleranceRaw,
			Bench1,
			Bench2,
			Bench3,
			Bench4,
			Bench5,
			21,
			getdate(),
			'N'
	from	cognosmetrics.dbo.cognosmetrics
	where	bitDb = 1

Select all Open in new window