Using faux sequence values in an insert loop 1

[DiverMarv]

Ok, we have a small collection of databases that are similar. Many tables need unique id values, but these id values must come from a pool in one central database. So no IDENTITY columns can be used. I have created a Sequence_Tbl in my central database, but now when I want to copy data from one database to another, where I used to use:
insert into db2.dbo.table
(column list except the identity col)
select column list except the identity col from db2.dbo.table

Now i can't do that, because we no longer use identity cols.

I've tried using a UDF, but you can't update a table in a UDF. So I tried creating a stored procedure, but only an extended stored procedure is allowed to be called from within a function. I've hit a conundrum here. Any thoughts? I could, of course, go the extended stored proc route, but I'd rather not.

Thanks

 

[gmmastros]

these id values must come from a pool in one central database

Can you explain this requirement? I assume it's because you want to make sure there are no duplicate id numbers in your separate databases. By having a pool of id numbers, you can make sure you don't get duplicates across your databases.

Instead, I would suggest that you use a GUID for your id values instead. This would replace your id's that you currently have. You can also have guids created for you by putting a default value on the table, and using NewId(). By using a GUID, you are guaranteed to have unique values in your separate databases (and will probably be more stable than your current method).

I realize that this may represent some drastic changes to your databases. If you are interested in this approach, let me know and I will clarify some issues regarding this approach.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[moskhan2]

DiverMarv, let me see if I understand you problem correctly

You have similar table in different databases
DB1 has table T1 with columns A(identity), B, C
DB2 has table T2 with columns D(identity), B, E
DB3 has table T3 with columns G(identity), B, F

You wish to store all the date of T1,T2 and T3 into T4 with the following columns
S(seed column) , B, C, E, F

There are 2 ideas I think might benefit you:
1) Have all the columns of T1, T2, T3 in you new table T4 along with a new column(S) that will be filled with a seed value from your central db. Once all the data is loaded, get rid of columns A, D, G from your new table. This way your load program should be very simple. (Or you could just mention the specific columns that you are loading)

2) You could reserve your seed values, so say 1-100,000 is reserved for all the records you are going to load in your db, and for any new records the seed will start from 100,001. I am not a fan of this suggestion, since there are gaps left, but I used it once cos it made my life easier

Since you are not using identity column in your new table, you could do the following in your stored procs
- lock the id(seed) record in your central db
- increment it by 1,
- store the new seed in a local variable
- unlock the seed immediately or right before your transaction end statement

but this is essentially like the identity column except that you never waste any numbers if you keep the seed locked during your transaction.

best
moskhan

PS: are you trying to setup a procedure so that you can copy data to your table (with the same schema) periodically ? for example copying production data to test environment weekly ? if so then lookem "set identity on/off" This can temporarily allow you to set the value of the identity column as you wish.