complex insert statement?

qwjunk · November 1st, 2004, 11:03 AM

im not that experienced at sql..
anyways this is the problem im facing ..

Im indicating the primary keys in each table along with the table with a '-'

ive 1 Main Table(Invoices - InvoiceGID) and 3 Other Tables
Companys - CompanyGID;
Branches - BranchGID;
Orders - OrderGID

The 3 Three Tables are related to the main Invoice table using these 3 relationship tables
CompanyInvoice - CompanyFID,InvoiceFID;
BranchInvoice - BranchFID,InvoiceFID;
OrderInvoice - OrderFID,InvoiceGID

CompanyGID is linked to CompanyFID & InvoiceGID is linked to InvoiceFID in the relationship table
CompanyInvoice

Now consider im entering a row into the Invoice Table (The Primary Autogenerated key is InvoiceGID)
for a BranchGID = 345, I can insert the data into Invoice Table fine.. but how do i relate the
BranchFID with the new Invoice Table Row in the Relationship table BranchInvoice.

I mean how do i put the value of BranchFID(345) & InvoiceFID(now autogenerated whose value i dont know)
into the relationship table while inserting the new row into Invoice table itself

how do i do this .. or is there some other way or is there a problem with how i understood the concept

if im not clear at explaining the problem pls do tell ...

joefawcett · November 1st, 2004, 01:07 PM

If I've understood then you need to use @@IDENTITY which gives you the autogenerated ID:

Code:

DECLARE @NewId INT
-- First insert
INSERT INTO tblInvoice data1, data2...
SET @NewId = @@IDENTITY
-- Second insert
INSERT INTO tblCompanyInvoice @NewId, data1, data2...
-- Other inserts...

--

Joe

qwjunk · November 2nd, 2004, 01:02 AM

thanx joe, i'll try this out..

one more thing .. ive explained my problem and asked for a solution which i thought was the best way to use the relationships factor in tables

now is there a better way to do this or is my understanding of the relationship table wrong?

qwjunk · November 2nd, 2004, 04:12 AM

joe one more thing ...

consider if im using an asp.net page as front end with more than 25~30 people using the function at the same time. it wont be a problem of using this sql statement(im not familiar with locking and its areas) ie. i wont get the InvoiceGID of some other person who added this at the same instant right?

mebe the qs is irrelevant to this point or mebe i dint get the working of the sql identity statement .. but do give an idea..