Change ID direction negative - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change ID direction negative

Author

Topic

Cass
Starting Member

9 Posts

Posted - 2008-07-07 : 07:28:17

Hi!
Table has ID colum by default IDENTITY (1,1)

But sometimes i need insert several rows this table where ID decrease negative.
For example by default increase 1,2,3,4 ... Then insert some rows where ID must be -1, -2, -3, -4 ...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-07 : 08:31:55

dare i ask why?

Em

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 08:48:33

This doesn't sound like a good idea at all....

However, this might work if you don't have to do this too often.


BEGIN TRANSACTION

SET IDENTITY_INSERT <yourTableName> ON

<do your manual insert(s) with a explicit SET of [Id] and specify negative Id's>

SET IDENTITY_INSERT <yourTableName> OFF

COMMIT TRANSACTION

I think the transaction will stop any other inserts that are happening to the table at the same time as your manual negative id inserts from failing / not acquiring an auto Id but I'm not 100% sure. someone here will be able to tell you.

-------------
Charlie

Cass
Starting Member

9 Posts

Posted - 2008-07-07 : 08:49:37

quote:
Originally posted by elancaster

dare i ask why?

hmm how can i excplain why ...
It's a little complicated project. Migratng MS Access db to SQL 2005EE as back-end db and the old solution work like that.
Negative ID-s are some total rows and positive is regular.

Cass
Starting Member

9 Posts

Posted - 2008-07-07 : 08:50:31

quote:
Originally posted by Transact Charlie

This doesn't sound like a good idea at all....

However, this might work if you don't have to do this too often.

Only once a mont

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 09:03:05

If you are performing all this work to migrate your db engine do you have the option of just adding a new column to your table DDL something like

[regularRow] BIT NOT NULL

because what you are currently suggesting is... not recommended.

-------------
Charlie

Cass
Starting Member

9 Posts

Posted - 2008-07-07 : 09:09:18

search a little here this site and maybe make use of this [url]http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server[/url]
I think there no matter is negative number -1 or -678
dbID sequence is 1,2,3,4, ... etc. and my custom ID is 1,2,-3,4, ... etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources