inserting into multiple tables from ASP from, need help ASAP

[belcom125]

I have 2 tables in the SQL 2000 database, Customers and CustomerAddress. The reason is because one customer can have multiple physical locations. I am building an ASP app that automates sales order process.

Anyway... I have a single form that has to create a new customer record but Company and Address are in different tables. When I do the first insert in automatically creates a new ID number for that customer but then I need to insert address info into Address table with that CustomerID that was just created. Basically I can't do both at once because I need to assign just created CustomerID to the FK field in the address table. Does it make sense ? ))

What's the best way to handle this ?

 

[pkailas]

After your first insert you can use:

SELECT Scope.Identity() as [IDNum]
[/code}]

That will retrieve your PK for you.. then you can use that value to do the second insert.

I love small animals, especially with a good brown gravy....

 

[belcom125]

can you please explain what exactly scope.identity() does? I can't find info on it.

 

[jdgonzalez]

Why not create a stored proc? Send all the fields you need to the stored proc and just run it as a transaction.

begin transaction
insert into myTable
   (myFields...)
values
   (myAspFields...)

select @myPk = scope_identity()

insert into mySecondTable
    (FK, myFields....)
values
    (@myPk, myAspFields...)
commit transaction

 

[jdgonzalez]

From BOL:

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

 

[SQLBill]

Just in case you don't know what BOL stands for:

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

-SQLBill

Posting advice: FAQ481-4875