| Author |
Topic |
|
raysefo
Constraint Violating Yak Guru
260 Posts |
 Posted - 2012-10-02 : 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
1974 Posts |
Posted - 2012-10-02 : 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 - 2012-10-02 : 14:11:12
|
| how about NEXT VALUE FOR? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-02 : 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 - 2012-10-02 : 15:30:32
|
| @chadmat,so if I use 1 column table, is there gonna be any table locks etc? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-02 : 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 - 2012-10-02 : 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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-03 : 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.sqlserver-dba.com |
|
|
|
raysefo
Constraint Violating Yak Guru
260 Posts |
Posted - 2012-10-03 : 07:43:36
|
| @chadmatif 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
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-10-03 : 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 - 2012-10-03 : 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
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-10-03 : 11:34:53
|
so, you split it into 2 tables? Put it back. Problem solved.  How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-04 : 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 recordsetsJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
How to get next value?