Create Identity field for temp table

This will start the identity column at a specified number that you pass ...
DBCC CHECKIDENT ( ‘databasename.dbo.tablename,RESEED, @StartNumber)

Ok,
I got this far, everything is there but the autonumber does not work, right now it populates the first record with the number I need but does not populate rest of fields.

Currently doing this, only showing 1st 7 fields, but it populates the number I need in first field, but I need my first record to end up there and then follow on down auto incrimenting.
SidtempId     KpiSid            TimePeriodSid  UnitId          Actualval     Targetval       ToleranceVal
17900      NULL      NULL      NULL      NULL      NULL      NULL
NULL      8587      6039      1      99.9      0.95      0.02
NULL      8591      6039      1      100      1      0.1
NULL      9651      6039      1      99.4      1      0.02
NULL      9652      6039      1      99.99      0.9997      0.02

Alter 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), KpiSid varchar(16), TimePeriodSid varchar(16), UnitId varchar(16), ActualVal float, TargetVal float,
							ToleranceVal Float, ToleranceRaw Float , Bench1 float, Bench2 Float, Bench3 Float, Bench4 Float, Bench5 Float, SecurityId int, 
							Date DateTime, Processed char(1))

	--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)

	--Insert data into the TempTable
	Insert #SidTemp (KPISID, TimePeriodSid, UnitId, ActualVal, TargetVal, ToleranceVal, ToleranceRaw, Bench1, Bench2, Bench3, Bench4, Bench5,
					SecurityID, date, Processed)
	Select	KPISID,
			TimePeriodSid,
			1,
			ActualVal,
			TargetVal,
			ToleranceVal,
			ToleranceRaw,
			Bench1,
			Bench2,
			Bench3,
			Bench4,
			Bench5,
			21,
			getdate(),
			'N'
	from	cognosmetrics.dbo.cognosmetrics
	where	bitDb = 1 

Select allOpen in new window

So, is it the SidTempID that you want to count automatically?
 

Hi,
Correct, I get the number I need from the Oraclbe db with line 13 and then i am insertign it into the temp db which I don't think is correct.

I put the check IDent last but it gives me this error, I think I am missing turning SidTempID into an identy field.

DBCC CHECKIDENT(#SidTemp, RESEED, @SidTemp)

1 row(s) affected)

(18 row(s) affected)
Msg 2560, Level 16, State 9, Procedure UpdateCognos, Line 42
Parameter 3 is incorrect for this DBCC statement.

(19 row(s) affected)

Hi,
ok, still get the error above that I mentioned, I cut out the insert of the temp vaiable before inserting records and put the DBCC at the end but I still get an error.  Of


(18 row(s) affected)
Msg 2560, Level 16, State 9, Procedure UpdateCognos, Line 40
Parameter 3 is incorrect for this DBCC statement.


Alter Proc dbo.UpdateCognos

AS

--Declare Variables
Declare @SidTemp varchar(16)

      --CREATE A TEMP TABLE TO INSERT THE TEMP SID
      Create Table #SidTemp (SidTempId int IDENTITY(1,1), KpiSid varchar(16), TimePeriodSid varchar(16), UnitId varchar(16), ActualVal float, TargetVal float,
                                          ToleranceVal Float, ToleranceRaw Float , Bench1 float, Bench2 Float, Bench3 Float, Bench4 Float, Bench5 Float, SecurityId int,
                                          Date DateTime, Processed char(1))

      --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 data into the TempTable
      Insert #SidTemp (KPISID, TimePeriodSid, UnitId, ActualVal, TargetVal, ToleranceVal, ToleranceRaw, Bench1, Bench2, Bench3, Bench4, Bench5,
                              SecurityID, date, Processed)
      Select      KPISID,
                  TimePeriodSid,
                  1,
                  ActualVal,
                  TargetVal,
                  ToleranceVal,
                  ToleranceRaw,
                  Bench1,
                  Bench2,
                  Bench3,
                  Bench4,
                  Bench5,
                  21,
                  getdate(),
                  'N'
      from      cognosmetrics.dbo.cognosmetrics
      where      bitDb = 1

--This will set the starting value for the column
      DBCC CHECKIDENT (#SidTemp,RESEED, @SidTemp)

Select * from #SidTemp

aelliso3:
You rock, thanks for the awesome help.

awesome ... I love it when it works out ... :-}

Works Great, thanks for the help