Inserting a unique id into each record of a bulk insert statement 2

[RebLazer]

In a stored procedure, I'm executing an insert statement that affects many rows. I'd like to have it insert a unique id into each row (i.e. "60" into the first affected row, "61" into the second affected row, etc.) I do not want to use an identity field, rather I want to use an integer value contained in table NEXT_KEY_VAL with the following little stored procedure:

[tt]CREATE PROCEDURE GET_NEXT_KEY_VALUE
@KEY_NM VARCHAR(30), @NEXT_KEY_VAL INT = -1 OUTPUT
AS
SET NOCOUNT ON
UPDATE
NEXT_KEY_VAL WITH (ROWLOCK)
SET
@NEXT_KEY_VAL = KEY_VAL = KEY_VAL + 1
WHERE
KEY_NM = @KEY_NM
RETURN @NEXT_KEY_VAL[/tt]

I may not use an identity field because I need this id field to be valid across similar tables - therefore, I need to keep one system of numbering - which is the purpose of that stored procedure, above. The stored procedure is called like this: [tt] GET_NEXT_KEY_VALUE 'name_of_id'[/tt]

So the question is, is it possible to write the insert statement without using a loop to insert a unique value into each row it inserts?

i.e. [tt]insert into dbo.grant_info (grant_#, GET_NEXT_KEY_VALUE 'pi_id', grant_amt, org_id,...[/tt]

(I don't think that syntax works - but that's the idea.) I tried assigning GET_NEXT_KEY_VALUE into a variable and then putting that variable into my insert statement, but it inserted the same value into each and every affected record. (Which is, of course, obvious, after some thought.)

Thanks very much!
Lazer

 

[royjimenez]

hi!

cann't u do it with a triggger ?

I haven't done that, it's only a idea.

 

[RebLazer]

I'll most certainly try it! Sounds like a great idea!

Thanks, Roy!
Lazer

 

[JayKusch]

Can you use the NewID() Function?

Thanks

J. Kusch

 

[RebLazer]

Mr. Kusch,

I believe that for the same reason I can't use an identity field, I also can't use NewID(). I need this id counter to be regularly incremented - regardless of who calls it.

For example: if 3 new records are inserted into table [tt]grant_info[/tt], they should get id's 1, 2, and 3. Now let's say 2 records are inserted into a similar (yet different) table [tt]grant_info_2[/tt], they should get id's 4 and 5. And if another user inserts 4 more records into that original table [tt]grant_info[/tt], they should get id's numbers 6, 7, 8, and 9.

So, Mr. Kusch, will NewID() work for me?

Thanks,
Lazer

 

[JayKusch]

Yes it will! It is unique for the entire database. In fact, Microsoft claims that it is globally unique because they use some bit of information about the network card as the seed value.


Thanks

J. Kusch

 

[JayKusch]

From Books Online:

The Transact-SQL NEWID function and the application API functions and methods generate new uniqueidentifier values from the identification number of their network card plus a unique number from the CPU clock. Each network card has a unique identification number; network card manufacturers guarantee that no other network card in the next 100 years will have the same number. The uniqueidentifier returned by NEWID is generated using the network card on the server. The uniqueidentifier returned by application API functions and methods is generated using the network card on the client.


Thanks

J. Kusch

 

[RebLazer]

And this is regardless of who inserts it, into which table?