SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get next value?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 10/02/2012 :  13:34:35  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 10/02/2012 :  14:11:12  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
how about NEXT VALUE FOR?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/02/2012 :  14:22:43  Show Profile  Reply with Quote
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.
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 10/02/2012 :  15:30:32  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
@chadmat,

so if I use 1 column table, is there gonna be any table locks etc?
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/02/2012 :  16:12:13  Show Profile  Visit chadmat's Homepage  Reply with Quote
Of course there will be locks. You wouldn't want 2 processes to get the same value.

-Chad
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 10/02/2012 :  21:38:28  Show Profile  Reply with Quote
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).
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 10/03/2012 :  01:30:42  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 10/03/2012 :  07:43:36  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
@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???
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 10/03/2012 :  09:26:38  Show Profile  Reply with Quote
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

Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 10/03/2012 :  09:36:01  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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.
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 10/03/2012 :  11:34:53  Show Profile  Reply with Quote
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

Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 10/04/2012 :  01:31:49  Show Profile  Visit jackv's Homepage  Reply with Quote
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.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000