amending a column to increment while inserting; then switching back

Question

Post reply

amending a column to increment while inserting; then switching back

DamianC

SSCrazy Eights

Points: 8006
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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Grant Fritchey SSC Guru Points: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Sergiy SSC Guru Points: 110192 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: 8006 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: 245813 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.

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.

DamianC SSCrazy Eights Points: 8006 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: 245813 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.

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.