We help IT Professionals succeed at work.

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

Open in new window

Comment
Watch Question

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

Author

Commented:
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 

Open in new window

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

Author

Commented:
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.

Author

Commented:
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)
Commented:
Try the code below and see if it helps ... an identity columns needs to be an int ... not a varchar, so the variable was changed and the IDENTITY(1,1) was changed in the CREATE TABLE
further down, you see the DBCC CHECKIDENT that will give the starting point for where to count, and I commented out the first INSERT #SidTemp statement because that was causing all the NULL values in the first row
 

Alter Proc dbo.UpdateCognos

AS

--Declare Variables
Declare @SidTemp int

	--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 temp Sid into tempTable
	--Insert #SidTemp  (SidTempID) VALUES (@SidTemp)

	--This will set the starting value for the column
	DBCC CHECKIDENT (#SidTemp,RESEED, @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 

Open in new window

Author

Commented:
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
Commented:
2 things ... change
Declare @SidTemp varchar(16)
to
Declare @SidTemp int
and move the DBCC command before the INSERT ... the RESEED has to happen before the values are inserted into the table.
 

Author

Commented:
aelliso3:
You rock, thanks for the awesome help.

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

Author

Commented:
Works Great, thanks for the help