scope_identity()

I have four tables:
1- customer details
2- bank details
3- car details
4- contract details

All these tables are linked with the contract ID which is the primary key in table 4 and foriegn key in the rest. When a new customer inputs all the above data from the VB front, I want table 4 to give contract ID with a autonumber, which should be sent to the other tables, so that the contract in all tables are the same so that it is linked properly....

I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager....

Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both

THANKSPLEASE HELP...I'm new to db design and really need this for my application to work....

THNAK YOU|||(...)
I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager....
I don't think you can use the EM for calling SCOPE_IDENTITY(). Besides, I think you'll need @.@.Identity instead. How is the data stored? All from VB? Do you use a stored procedure?

(...)
Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both
Which one is unique?|||Yeah all the data is inputted through VB. I previously tried to join the 4 tables into a view and after all the data was entered (and so the contract ID would be the same in each table)...however you can not update a view like that!!!

Would it work if I joined the tables using a stored procedure...how would I use @.@.idenity....what I want it to do is eg,

in Contract table contract ID 4 = customer ID 1
then in the customer table contract ID 4 should be respresently by customer 1 personal details|||If a new row is inserted into a table with the identity column, @.@.identity holds the latest identity value inserted, fe:
set nocount on

create table tab1 (myint integer identity (10,1), myvar varchar(10))
go

insert into tab1 (myvar) values ('aa')
select * from tab1
select @.@.identity

insert into tab1 (myvar) values ('aa')
select @.@.identity

go

select * from tab1
go

drop table tab1
go

I think there might be an alternative for the hughe lump of a join trying the update all tables all at once (which I think won't work anyway): I'm thinking of updating/inserting each table seperately from the others and have a commit/rollback to have either the fresh data committed on success or rollbacked in case of failure. See BOL for commit examples.|||You need to actually change Kaiowas example back to SCOPE_IDENTITY(). People should really stop using @.@.identity. If you have a trigger on your table that inserts into another table with an identity column, you just captured the identity column of the table in the trigger instead of the one you meant to capture. This is the difference between @.@.identity and SCOPE_IDENTITY().

Basically, in your stored procedure, insert into contract details, and SET @.variable = SCOPE_IDENTITY(), which will be the IDENTITY you just inserted into contract details. Then populate the other tables with the values they need. If you need to, have seperate stored procedures. Return the @.variable to your application as the output of the first insert. Use that in your future inserts.

You should be doing everything through stored procedures btw.|||Anyone want to mention that "prepopulating" tables is a bad idea?

Are you collecting all the data at once?

Is there a 1 to many relationship?

Don't you have something more meaningful to identify a customer?|||Also, think about this...when you want to look up a customer in the future, what are you going to use to identify them as the correct customer?

Probably from a pick list right?

Whatever you see on that list that makes you know it the right person...that's your key...

Not some bs number...

It's like...

ok here's a list of arbitray numbers...pick the right one...no way

Should add this to the list

The Devils Spawn (http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx)|||You need to actually change Kaiowas example back to SCOPE_IDENTITY(). People should really stop using @.@.identity. If you have a trigger on your table that inserts into another table with an identity column, you just captured the identity column of the table in the trigger instead of the one you meant to capture. This is the difference between @.@.identity and SCOPE_IDENTITY().
And to think how often I actually used @.@.identity... lucky for me there's no triggering here, it's all in sp's!|||The customer has a customer ID which is used to differentate between each user. However, the four tables are linked through a contract ID, which is the pk (and autonumber) in contract table. When a new contract is created the data is stored over four tables... I want to be able to ensure the contract ID given in the contract table (autonumber) is copied to the other three pages....

How do I populate the other tables with the scope_identity i.e. the contract ID (autonumber) ??|||you could put it in a local variable, fe:

declare @.my_identity as integer

insert into tab1 (myvar1) values ('example')
set @.my_identity = scope_identity()

insert into tab2 (myint2, myvar2) values (@.my_identity, 'scope_identity')

You may want to add some error checking as well, change the set into a select allowing multiple variables to be filled in one statement. See @.@.error in BOL for this.

Unless there's this thing called scope_error() or @.@.scope_error, er, oh no wait, maybe not.. ugh.. or perhaps you do. Oh brother!

Well, I guess I shouldn't have started reading about that Surrogate Key-article Brett was so kind to point out, I think I'll be running for politics now.|||I gotta ask...

Are all the columns in the other tables nullable?

If ContractId is a FK, and part of a composite PK, what the other non nullable PK compononent?

Drumroll please......