Author 
Topic 

raysefo
Constraint Violating Yak Guru
260 Posts 
Posted  10/02/2012 : 13:34:35

Hi,
I am using sql server 2008. There are two tables, lets say table A and table B. On those two tables, they have identity columns (lets call it column1) Every time I insert a row to those tables, those columns get the +1 value. So they have duplicate values. (They both have 1,2,3 etc) Now what I would like to do is to have a common identity field (table) I mean if data inserted to table A then this column1 should be 1 and then if data inserted to table B then column1 of table B should be 2 and so on.
How can I manage this? I read something about "next value for.." but I am not sure.
Best Regards. 

chadmat
The Chadinator
USA
1974 Posts 
Posted  10/02/2012 : 13:59:44

Just create a 1 column table and a Stored Proc or function to increment the value, and return it. Then use that proc or function to do your inserts.
Chad 


raysefo
Constraint Violating Yak Guru
260 Posts 
Posted  10/02/2012 : 14:11:12

how about NEXT VALUE FOR? 


sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts 
Posted  10/02/2012 : 14:22:43

quote: Originally posted by raysefo
how about NEXT VALUE FOR?
That is available only in SQL 2012.
You could create a third table with the identity column and have the primary keys of the two existing tables be foreign keys from the new table. Makes things more complicated though. 


raysefo
Constraint Violating Yak Guru
260 Posts 
Posted  10/02/2012 : 15:30:32

@chadmat,
so if I use 1 column table, is there gonna be any table locks etc? 


chadmat
The Chadinator
USA
1974 Posts 
Posted  10/02/2012 : 16:12:13

Of course there will be locks. You wouldn't want 2 processes to get the same value.
Chad 


LoztInSpace
Aged Yak Warrior
940 Posts 
Posted  10/02/2012 : 21:38:28

If the tables are unrelated then it doesn't matter if they have the same identity because you know from the table which they are. If they are related then you should most likely use the same number when you relate them. Sounds very much like a suspect data model. Can you elaborate more? (BTW if you were on SQL 2012 then a SEQUENCE would be exactly what you're after, but what you're doing still sounds dodgy). 


jackv
Flowing Fount of Yak Knowledge
United Kingdom
2124 Posts 
Posted  10/03/2012 : 01:30:42

Whether you integrate as 1 table or join the primary keys onto a third table will depend on a number of factors. It is worth going back to the original modelling diagrams and working out what you're trying to acheive. If you could describe your scenario with some more detail 
Jack Vamvas  http://www.sqlserverdba.com 


raysefo
Constraint Violating Yak Guru
260 Posts 
Posted  10/03/2012 : 07:43:36

@chadmat if there will be table locks so this means if two users want to fill the forms at the same time, then one of them will not get the next number??? 


DonAtWork
Flowing Fount of Yak Knowledge
2167 Posts 
Posted  10/03/2012 : 09:26:38

A table lock usually lasts for a VERY tiny amount of time. Nobody should have any problem getting their number. I am curious as to why these tables, which are unrelated, need to relate? I join the others in asking for the bigger picture here.
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



raysefo
Constraint Violating Yak Guru
260 Posts 
Posted  10/03/2012 : 09:36:01

Hi,
Those two tables are expense tables, one for training expenses and the other is for all other expenses. At first, there was only one table (form) for all kind of expenses and I have a identity field on this table. In order to send those expenses to SAP I need this unique number. I suggest them to use an identity field which starts from 100000 and there will not be a conflict (same numbers) at least for 15 years. (for 2 years there are 2699 recors entered so far) They want those numbers to be consecutive persistently. This is all about. 


DonAtWork
Flowing Fount of Yak Knowledge
2167 Posts 

jackv
Flowing Fount of Yak Knowledge
United Kingdom
2124 Posts 
Posted  10/04/2012 : 01:31:49

Look at your data model. It may be easy to put them in as 1 table  and then create a Foreign Key to a "Expensecategory" table  that way the sql statement can join the two tables . You can create different recordsets
Jack Vamvas  http://www.sqlserverdba.com 



Topic 
