October 11, 2016 at 9:33 am
Hello
I have a table that has a column id with a data type of bigint
This is built using Cognos data manager which has a funny little (non-identity) way of incrementing the counter
There's quite a few build routines that are reliant upon this been bigint
i.e. I want to leave this Cognos DM build intact and use SSIS for the next part
I have a new datasource and want to use this to append to the existing table
Can I switch to identity, upsert, then switch back?
Or is there some other method I should be using
I've tried
set identity_insert <table> on
This doesn't work
Ideally, I want to use T-SQL
Thanks
Damian.
- Damian
October 11, 2016 at 10:15 am
The IDENTITY property can't be added to an existing column nor removed from a column.
You could add an identity column to the table, if it doesn't have one, and then adjust the next identity value to be assigned, using:
DBCC CHECKIDENT ( ... RESEED )
Then use that value to assign a value to another column, possibly in a trigger. Then you could reset the identity value with another RESEED.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 11, 2016 at 12:04 pm
This is the 2008 forum, so I suspect this won't help, but if you're on 2012 or better, you could look at using SEQUENCE to possibly do what you're looking for.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 11, 2016 at 3:14 pm
You may add ROW_NUMBER to the recordset being inserted and add its values to MAX (ID ) of the table:
begin transaction
declare @MaxID bigint
select @MaxID + MAX(ID) From TargetTable (TABLOCKX)
INSERT INTO TargetTable
(ID, ...)
SELECT @MaxID + ROW_NUMBER() over (...), ...
FROM Sourse
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK
_____________
Code for TallyGenerator
October 12, 2016 at 4:03 am
Thanks for the advice
Unfortunately, it is 2008 so SEQUENCE is not an option
I'm going to look into the proposal by sergiy
For my purposes, I think I can remove all the relevant records and repopulate from the max id counter
upserts and attempting to use an SCD that holds history is probably going to over-complicate it
I'll hold history elsewhere and just focus on getting the current data into this table
Thanks
- Damian
October 12, 2016 at 6:11 am
Sergiy (10/11/2016)
You may add ROW_NUMBER to the recordset being inserted and add its values to MAX (ID ) of the table:
begin transaction
declare @MaxID bigint
select @MaxID + MAX(ID) From TargetTable (TABLOCKX)
INSERT INTO TargetTable
(ID, ...)
SELECT @MaxID + ROW_NUMBER() over (...), ...
FROM Sourse
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK
Nice idea, Sergiy.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2016 at 7:55 am
Using your suggestion sergiy, I've managed to get it working
One question
You have:
declare @MaxID bigint
select @MaxID + MAX(ID) From TargetTable (TABLOCKX)
This returns NULL
I've amended it to (changed + for =)
declare @MaxID bigint
select @MaxID = MAX(ID) From TargetTable (TABLOCKX)
Was it a typo or have I missed something fundamental?
Thanks
Damian.
- Damian
October 12, 2016 at 8:30 am
DamianC (10/12/2016)
Using your suggestion sergiy, I've managed to get it workingOne question
You have:
declare @MaxID bigint
select @MaxID + MAX(ID) From TargetTable (TABLOCKX)
This returns NULL
I've amended it to (changed + for =)
declare @MaxID bigint
select @MaxID = MAX(ID) From TargetTable (TABLOCKX)
Was it a typo or have I missed something fundamental?
Thanks
Damian.
Typo. Well spotted.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply