February 6, 2006 at 4:17 pm
Hi there,
A big problem I'm having trouble getting my head around:
I am trying to import data into three tables at the same time, two of the tables have a primary key of an autonumber type, the third table is a link table, which records the new @@identity value from each table into its own.
This works fine when I am only working on one insert at a time. Though I have some occasions where I will need to insert one record in table, two records in another table, and record in the third table the link, i.e.
Table 1
X1
Table 2
Y1
Y2
Table 3
X1 Y1
X1 Y2
I tried the above with the following code, which inserts into table 1 and table 2 multiple values, though @@Identity only returns the last autonumber:
DECLARE @INV_ID as Integer
DECLARE @GTRANS_ID as Integer
DECLARE @VTRANS_ID as Integer
INSERT INTO Purchase_Invoice
(Inv_Date,Supplier_ID,Inv_Ref,Notes)
SELECT Date, Cr_Code,Inv_Ref,Notes
FROM [Purchase Invoices]
SET @INV_ID = (SELECT @@IDENTITY)
INSERT INTO [Transaction]
(Trans_Date,Dr_Code,Cr_Code,Amount,Notes)
SELECT Date,Dr_Code,Cr_Code,Gross,Notes
FROM [Purchase Invoices]
SET @GTRANS_ID = (SELECT @@IDENTITY)
INSERT INTO [Transaction]
(Trans_Date,Dr_Code,Cr_Code,Amount,Notes)
SELECT Date,Dr_Code,Cr_Code,VAT,Notes
FROM [Purchase Invoices]
SET @VTRANS_ID = (SELECT @@IDENTITY)
INSERT INTO Purchase_Inv_Trans
(Inv_ID, Transaction_ID)
VALUES (@INV_ID,@GTRANS_ID)
INSERT INTO Purchase_Inv_Trans
(Inv_ID, Transaction_ID)
VALUES (@INV_ID,@VTRANS_ID)
Its a hard problem to explain, if you need any more clarification, please let me know!
Many thanks to the person that can help!
Glenn
February 6, 2006 at 4:22 pm
If you are INSERTing 2 or more records, then @@Identity is not a workable solution, and you'll need to use set-based operations.
This will typically require identifying the natural, unique identifiers for Purchase_Invoices and Transactions.
February 6, 2006 at 4:44 pm
How would I go about identifying those?
My aim in the above was to transfer values from one table (Purchase_Invoices) into three tables, Purchase_Invoice, Transaction, and Purch_Inv_Transaction.
I want to query the Purchase_Invoices table, so that for each record, part of the data is recorded into Purchase_Invoice, the remaining part of the data is split into two transactions and inserted into the transaction table, and the third table records the link between the two.
February 6, 2006 at 4:51 pm
The SQL for the "link table" (intersection table) would look something like this:
INSERT INTO Purchase_Inv_Trans
(Inv_ID, Transaction_ID)
SELECT
pi.InvoiceID, t.TransactionID
FROM [Purchase Invoices] As src
INNER JOIN Purchase_Invoice As pi
On (pi.NaturalKey1 = src.NaturalKey1 AND
pi.NaturalKey2 = src.NaturalKey2 AND
etc etc )
INNER JOIN Transaction As t
ON (t.NaturalKey1 = src.NaturalKey1 AND
t.NaturalKey2 = src.NaturalKey2 AND
etc etc )
In order to make it work, you need to specify which columns uniquely identify and link back to your source [Purchase Invoices] table.
For example, perhaps (Inv_Date,Supplier_ID,Inv_Ref) uniquely identifies an Invoice ? If so, that's your 3 part natural key.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply