Copying Primary Key to Foreign Key

Copying Primary Key to Foreign Key

Post by Davi » Wed, 17 Oct 2001 23:43:18



Hello,

Please someone help me!  I might be the biggest idiot in the world -
but I can not figure this out to save my life.

I have a table - with an autogenerated primary key, and I want each
record in this table to link to multiple records in another table, but
I can not figure out how to copy the data from the first table
automatically into the second table

Here is an example -

Tbl1
InvoiceID (This is the Primary Key)
CustNumber

Tbl2
LineID (PK for 2nd tbl)
InvoiceID (FK from Tbl1)

Each Line Item needs to be related back to a specific invoiceID - so
how do I get the InvoiceID to automatically insert itself into tbl2?

Any Help is greatly appreciated

Thanks

David Fallow

 
 
 

Copying Primary Key to Foreign Key

Post by Zachary Well » Wed, 17 Oct 2001 23:52:20


It doesn't "automatically" insert itself, you have to do it
programmatically.

Zach


Quote:> Hello,

> Please someone help me!  I might be the biggest idiot in the world -
> but I can not figure this out to save my life.

> I have a table - with an autogenerated primary key, and I want each
> record in this table to link to multiple records in another table, but
> I can not figure out how to copy the data from the first table
> automatically into the second table

> Here is an example -

> Tbl1
> InvoiceID (This is the Primary Key)
> CustNumber

> Tbl2
> LineID (PK for 2nd tbl)
> InvoiceID (FK from Tbl1)

> Each Line Item needs to be related back to a specific invoiceID - so
> how do I get the InvoiceID to automatically insert itself into tbl2?

> Any Help is greatly appreciated

> Thanks

> David Fallow


 
 
 

Copying Primary Key to Foreign Key

Post by m.mine » Thu, 18 Oct 2001 00:37:31


Use something like this:
---------------------

CREATE TABLE Tbl1
(
    InvoiceID INT IDENTITY(1,1) PRIMARY KEY,
    CustNumber CHAR(32)
)
GO
CREATE TABLE Tbl2
(
    LineID INT IDENTITY(1,1) PRIMARY KEY,
    InvoiceID INT,
    LineData VARCHAR(128) NULL
)
GO

BEGIN TRANSACTION


-- Create new invoice:
INSERT INTO
    Tbl1( CustNumber )
VALUES
    ( '122F3' )

-- Collect generated InvoiceID:

-- Insertind lines data:
INSERT INTO
    Tbl2( InvoiceID, LineData )
VALUES

INSERT INTO
    Tbl2( InvoiceID, LineData )
VALUES

-- ...

COMMIT TRANSACTION

------------------------------
M.Minev


Quote:> Hello,

> Please someone help me!  I might be the biggest idiot in the world -
> but I can not figure this out to save my life.

> I have a table - with an autogenerated primary key, and I want each
> record in this table to link to multiple records in another table, but
> I can not figure out how to copy the data from the first table
> automatically into the second table

> Here is an example -

> Tbl1
> InvoiceID (This is the Primary Key)
> CustNumber

> Tbl2
> LineID (PK for 2nd tbl)
> InvoiceID (FK from Tbl1)

> Each Line Item needs to be related back to a specific invoiceID - so
> how do I get the InvoiceID to automatically insert itself into tbl2?

> Any Help is greatly appreciated

> Thanks

> David Fallow

 
 
 

Copying Primary Key to Foreign Key

Post by Darren Brinksneade » Thu, 18 Oct 2001 01:18:55


One option, you can use use a trigger...

CREATE TRIGGER trg_Tbl1_Insert ON Tbl1
FOR INSERT
AS
INSERT INTO Tbl2 (InvoiceID)
SELECT InvoiceID
  FROM inserted
GO

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT


Quote:> Hello,

> Please someone help me!  I might be the biggest idiot in the world -
> but I can not figure this out to save my life.

> I have a table - with an autogenerated primary key, and I want each
> record in this table to link to multiple records in another table, but
> I can not figure out how to copy the data from the first table
> automatically into the second table

> Here is an example -

> Tbl1
> InvoiceID (This is the Primary Key)
> CustNumber

> Tbl2
> LineID (PK for 2nd tbl)
> InvoiceID (FK from Tbl1)

> Each Line Item needs to be related back to a specific invoiceID - so
> how do I get the InvoiceID to automatically insert itself into tbl2?

> Any Help is greatly appreciated

> Thanks

> David Fallow

 
 
 

Copying Primary Key to Foreign Key

Post by Davi » Thu, 18 Oct 2001 04:43:22


Right - I'm sorry - I knew that, but how do I program it to
automatically insert the required data?

> It doesn't "automatically" insert itself, you have to do it
> programmatically.

> Zach



> > Hello,

> > Please someone help me!  I might be the biggest idiot in the world -
> > but I can not figure this out to save my life.

> > I have a table - with an autogenerated primary key, and I want each
> > record in this table to link to multiple records in another table, but
> > I can not figure out how to copy the data from the first table
> > automatically into the second table

> > Here is an example -

> > Tbl1
> > InvoiceID (This is the Primary Key)
> > CustNumber

> > Tbl2
> > LineID (PK for 2nd tbl)
> > InvoiceID (FK from Tbl1)

> > Each Line Item needs to be related back to a specific invoiceID - so
> > how do I get the InvoiceID to automatically insert itself into tbl2?

> > Any Help is greatly appreciated

> > Thanks

> > David Fallow

 
 
 

Copying Primary Key to Foreign Key

Post by Zachary Well » Thu, 18 Oct 2001 04:52:14


I'm not following your question. If you have data that needs to be inserted
into both tables, then you insert the parent data first, retrieve the ID
that was created, then insert the child data. Something like this:

insert into Tbl1 (fields) values (values)


That's a rough outline. You'll want to think about wrapping that up in a
transaction if appropriate for your situation.

Zach


> Right - I'm sorry - I knew that, but how do I program it to
> automatically insert the required data?




> > It doesn't "automatically" insert itself, you have to do it
> > programmatically.

> > Zach



> > > Hello,

> > > Please someone help me!  I might be the biggest idiot in the world -
> > > but I can not figure this out to save my life.

> > > I have a table - with an autogenerated primary key, and I want each
> > > record in this table to link to multiple records in another table, but
> > > I can not figure out how to copy the data from the first table
> > > automatically into the second table

> > > Here is an example -

> > > Tbl1
> > > InvoiceID (This is the Primary Key)
> > > CustNumber

> > > Tbl2
> > > LineID (PK for 2nd tbl)
> > > InvoiceID (FK from Tbl1)

> > > Each Line Item needs to be related back to a specific invoiceID - so
> > > how do I get the InvoiceID to automatically insert itself into tbl2?

> > > Any Help is greatly appreciated

> > > Thanks

> > > David Fallow

 
 
 

Copying Primary Key to Foreign Key

Post by raju girdh » Wed, 24 Oct 2001 05:40:52


Hello David,
You could define an INSERT Trigger on tbl1 that would execute an INSERT
statement on tbl2.

Hope this helps.

Raju Girdher MCSE
SQL Support Engineer, Microsoft

Please reply only to newsgroups. When posting, please state the version of

error message text received, if any.

Disclaimer:
This posting is provided AS IS with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.