May 17, 2012 at 9:52 am
Hello
I have a table Allocations with coulmns AllocationID , PaymentID , ….
AllocationID is a PK.
Depending on certain condition I have to insert multiple records in Allocations table.
I get the MAX AllocationID from the Allocations table
@NextAllocationID = Max(AllocationID) + 1
Create Table #Temp (
ID INT IDENTITY(@NextAllocationID ,1),
PaymentId Int,
CId Int,
…
)
The above gives error so I tried doing it this way-->
Create Table #Temp (
ID INT IDENTITY(1,1),
PaymentId Int,
CId Int,
…
)
DBCC CHECKIDENT (#Temp, RESEED, @NextAllocationID );
Insert into #Temp (PaymentId, Cid,…)
Select a.PaymentId, a.Cid,… from Allocations a
where Payment_Id = @XXX
…..
Insert into Allocations(Allocation_Id,PaymentId,CId,…
)
Select ID, @NewPaymentId, Claim_Id,….. from #Temp
But using DBCC checkident gives error, user 'guest' does not have permission to run dbcccheckident on object #temp , violation of primary key..can not insert duplicate key....
How can this be achieved...I dont want to use a loop or cursor.
Thanks for any help.
Janki
-----------------
May 17, 2012 at 10:19 am
Don't use identity, use ROW_NUMBER() instead. You can start counting from any requested value...
May 17, 2012 at 10:26 am
And if you really insist, then the following example should give you an idea:
declare @n int
set @n = 5 -- I want my inserts start from 5
create table #t (id int identity(1,1), val char(1))
set identity_insert #t ON
insert #t (id) values (@n-1)
set identity_insert #t OFF
delete #t
insert #t values ('a')
select * from #t
May 17, 2012 at 10:35 am
SJanki (5/17/2012)
I have a table Allocations with coulmns AllocationID , PaymentID , ….AllocationID is a PK.
Depending on certain condition I have to insert multiple records in Allocations table.
I get the MAX AllocationID from the Allocations table
@NextAllocationID = Max(AllocationID) + 1
Create Table #Temp (
ID INT IDENTITY(@NextAllocationID ,1),
PaymentId Int,
CId Int,
…
)
The above gives error...
I'm a little confused. Why don't you just ALTER the table and set AllocationID as an IDENTITY field? You wouldn't have to fuss with variables, then.
May 17, 2012 at 10:40 am
Brandie Tarvin (5/17/2012)
...I'm a little confused. Why don't you just ALTER the table and set AllocationID as an IDENTITY field? You wouldn't have to fuss with variables, then.
Could you please demonstrate how to write ALTER table with setting column as an IDENTITY? Will it execute?
May 17, 2012 at 10:49 am
Eugene Elutin (5/17/2012)
Brandie Tarvin (5/17/2012)
...I'm a little confused. Why don't you just ALTER the table and set AllocationID as an IDENTITY field? You wouldn't have to fuss with variables, then.
Could you please demonstrate how to write ALTER table with setting column as an IDENTITY? Will it execute?
Basically you have to recreate the column then drop the old one. Which only works if you are not using numbers based off of other data.
CREATE TABLE dbo.BrandieTemp (MyID INT NOT NULL, TestCol1 VARCHAR(35),
TestCol2 VARCHAR(10));
GO
INSERT INTO dbo.BrandieTemp (MyID, TestCol1, TestCol2)
VALUES (1,'Mickey','Mouse'), (2,'Donald','Duck'), (3,'Goofy','who?');
SELECT MyID, TestCol1, TestCol2 FROM dbo.BrandieTemp;
ALTER TABLE dbo.BrandieTemp
ADD MyID2 INT IDENTITY(1,1);
GO
SELECT MyID, TestCol1, TestCol2, MyID2 FROM dbo.BrandieTemp;
ALTER TABLE dbo.BrandieTemp
DROP COLUMN MyID;
GO
DROP TABLE dbo.BrandieTemp;
EDIT: It helps if you rename the "new" column after you drop the old one. I didn't include that in my code, though.
May 17, 2012 at 10:53 am
Here's another method:
CREATE TABLE dbo.BrandieTemp (MyID INT NOT NULL, TestCol1 VARCHAR(35),
TestCol2 VARCHAR(10));
GO
INSERT INTO dbo.BrandieTemp (MyID, TestCol1, TestCol2)
VALUES (1,'Mickey','Mouse'), (2,'Donald','Duck'), (3,'Goofy','who?');
SELECT MyID, TestCol1, TestCol2
INTO #MyTemp
FROM dbo.BrandieTemp;
ALTER TABLE dbo.BrandieTemp
DROP COLUMN MyID;
GO
ALTER TABLE dbo.BrandieTemp
ADD MyID INT IDENTITY(1,1);
GO
TRUNCATE TABLE dbo.BrandieTemp;
SET IDENTITY_INSERT dbo.BrandieTemp ON;
INSERT INTO dbo.BrandieTemp (MyID, TestCol1, TestCol2)
SELECT MyID, TestCol1, TestCol2
FROM #MyTemp;
SET IDENTITY_INSERT dbo.BrandieTemp OFF;
DROP TABLE #MyTemp;
DROP TABLE dbo.BrandieTemp;
EDIT: One thing to keep in mind with both methods. Foreign Keys will screw with you if you're not careful. You can break stuff doing both methods, especially if you use Method 1 and forget to rename the column when you're done.
May 17, 2012 at 12:03 pm
OP wanted to start IDENTITY from the position calculated/given in variable. The only way to do this is to INSERT with INSERT_IDENTITY ON. It's impossible to do it with ALTER TABLE, as you still have the same problem: IDENTITY sill not take value for a seed form variable.
As I suggested in my first reply, for the purpose OP is trying to use it, the best option will be using ROW_NUMBER() when populating temp-table instead of having identity column in it.
May 17, 2012 at 12:22 pm
Eugene Elutin (5/17/2012)
OP wanted to start IDENTITY from the position calculated/given in variable. The only way to do this is to INSERT with INSERT_IDENTITY ON. It's impossible to do it with ALTER TABLE, as you still have the same problem: IDENTITY sill not take value for a seed form variable.As I suggested in my first reply, for the purpose OP is trying to use it, the best option will be using ROW_NUMBER() when populating temp-table instead of having identity column in it.
Thank you all for the discussions. For the time being I used insert identity on off..I'll try with row_number().
May 17, 2012 at 12:53 pm
Eugene, I appreciate what you're saying, but please watch how you word it. Your tone in your last post came across very harsh and .. well, there's another poster on this forum that I could compare you to, but I won't.
You asked me how I would ALTER the table. I gave you my examples. They may not work for you, but they have worked for me when I needed them. And I didn't post them as the Be-All / End-All solution. I added caveats to my posts.
In circumstances like these, it is up to the OP to decide which solution is best. All we can do is offer them.
May 17, 2012 at 3:51 pm
Eugene Elutin (5/17/2012)
Don't use identity, use ROW_NUMBER() instead. You can start counting from any requested value...
Seconded...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2012 at 3:17 am
Brandie Tarvin (5/17/2012)
Eugene, I appreciate what you're saying, but please watch how you word it. Your tone in your last post came across very harsh and .. well, there's another poster on this forum that I could compare you to, but I won't....
I don't have any tone in my messages, being non-native English speaker my language is quite plane. But sorry, if I somehow offended you. I just wanted to distinct that it's impossible to use ALTER TABLE to ALTER column to have IDENTITY on it and there is no way to use variable to set an identity seed.
If you have a look my example you'll find that it's not different to yours as the change of seed is achieved by INSERT_IDENTITY ON.
And please, do not try compare me to JC, I'm not asking people to read ISO standards and I'm not marketing my books here... :hehe:
May 18, 2012 at 8:04 am
Haven't tried it in a very long time but, if memory serves, you can use a dynamic DBCC CHECKIDENT to reseed a temp table. Changing the increment will be the bugger.
Still, I see no need for it on this particular problem. I agree with Eugene's orginal recommendation to simply use ROW_NUMBER().
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply