amending a column to increment while inserting; then switching back

Question

amending a column to increment while inserting; then switching back

DamianC

SSCrazy Eights

Points: 8023
More actions
October 11, 2016 at 9:33 am

#331206

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101134 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 2

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sergiy SSC Guru Points: 110208 More actions · Answer 3

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

DamianC SSCrazy Eights Points: 8023 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DamianC SSCrazy Eights Points: 8023 More actions · Answer 6

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 7

DamianC (10/12/2016)
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.

Typo. Well spotted.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel