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.
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
This will start the identity column at a specified number that you pass ...
DBCC CHECKIDENT ( ‘databasename.dbo.tablenam
DBCC CHECKIDENT ( ‘databasename.dbo.tablenam
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
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
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.
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)
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)
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
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
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_VALU
--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.cognosme
where bitDb = 1
--This will set the starting value for the column
DBCC CHECKIDENT (#SidTemp,RESEED, @SidTemp)
Select * from #SidTemp
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_VALU
--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.cognosme
where bitDb = 1
--This will set the starting value for the column
DBCC CHECKIDENT (#SidTemp,RESEED, @SidTemp)
Select * from #SidTemp
Premium Content
You need an Expert Office subscription to comment.Start Free Trial