|
Subject:
|
complex insert statement?
|
|
Posted By:
|
qwjunk
|
Post Date:
|
11/1/2004 10:03:52 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 ...
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
11/1/2004 12:07:01 PM
|
If I've understood then you need to use @@IDENTITY which gives you the autogenerated ID:
DECLARE @NewId INT
-- First insert
INSERT INTO tblInvoice data1, data2...
SET @NewId = @@IDENTITY
-- Second insert
INSERT INTO tblCompanyInvoice @NewId, data1, data2...
-- Other inserts...
--
Joe
|
|
Reply By:
|
qwjunk
|
Reply Date:
|
11/2/2004 12:02:30 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?
|
|
Reply By:
|
qwjunk
|
Reply Date:
|
11/2/2004 3:12:21 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..
|
