Get an identity increment value before insertion? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Get an identity increment value before insertion?

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2011-04-13 : 05:32:28

I have two tables.

table tmpImport

table Asset

Table asset gets populated by an insert statement e.g.
Insert into table Asset
Select * from tmpImport
truncate table tmpImport

Table Asset
primary key is an automatic Identity field called AssetID that increments automatically by 1 (Identity increment by 1).
There is another field in this table called RefNo. This value is called ID/SPXX where XX should be the value of the autonumber Id field.

I can't make it an auotcalculated fields since it is only applicable for a spesific scenario and user are allowed to changed the refno afterwards even to blank so a update script for blank refno afterwards won't help. I don't want trigger.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-13 : 06:23:52

Pseudo code..

DECLARE @ID
INSERT Table1 () VALUES ()
SET @ID = SCOPE_IDENTITY()

UPDATE Table1 SET Col2 = '' WHERE Col1 = @ID

N 56°04'39.26"
E 12°55'05.63"

madlo
Starting Member

41 Posts

Posted - 2011-04-13 : 06:47:15

WoMy problem is that the existing sql can insert multiple at a time with the statement
Insert into table Asset
Select * from tmpImport
truncate table tmpImport

SCOPE_IDENTITY() would then only give me that last identity value ie. 100

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-13 : 15:38:32

Use @@ROWCOUNT to get the number of records inserted.

DECLARE @ID INT, @Cnt INT
INSERT dbo.Table1 () VALUES ()
SET @ID = SCOPE_IDENTITY(), @Cnt = @@ROWCOUNT * IDENT_INCR('dbo.Table1')

UPDATE Table1 SET Col2 = '' WHERE Col1 BETWEEN @ID - @Cnt + 1 AND @ID

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources