How to insert into foreign key table

  • Hi,

    I have a one to many relationship tables, Customer and Order. I want to make sure that CustomerID from the customer table goes into the Order table automatically for each insert I execute.

    Could someone please advise on the best way to write the insert statement.

    Thanks

    Customer

    CustomerID INT IDENTITY(1,1), ---primary key

    FirsName,

    SecondName,

    Order

    OrderID INT IDENTITY(1,1),

    CustomerID INT ---foreign key

  • There are a lot of options, depending on what you want to do with the exact data.

    Most importantly, why insert automatically into Orders just because you add to Customers? Most applications add to Customers, pull the ID from that (generally the query will output/return/select the inserted identity value using scope_identity), and then procede to process the order, if there is one.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I'm using a stored procedure to do the insert and in this case when a customer places an order I want to capture the customerID and insert that into the order table as the foreign key....I haven't used scope_identity function before so I'm trying to find out the correct syntax for it

  • Sounds like CustomerID should be a parameter to the insert sproc.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi have a look at the below , i think you can get some idea.

    declare @py table(cid int identity,fname varchar(50))-- Customer table

    declare @sy table(Oid int identity,Cid int,fname varchar(50))-- Order table

    /*Customer details are here*/

    Insert into @py values ('FnameC1'),('FnameC2')

    /*Customer order details are here with CustomerId as FK of @py*/

    /*Note: I have not used FK, PK since it is Temp table this is how the relationship will be working */

    Insert into @sy values (1,'FnameC1O1'),(1,'FnameC1O2'),(2,'FnameC2O1'),(2,'FnameC2O2'),(2,'FnameC2O3'),(2,'FnameC2O4')

    Insert into @sy values (3,'FnameC3O1'),(3,'FnameC3O2') --should fail since there is no entry for 3 like wise it goes on

    Select * from @py

    Select * from @sy

    As Sean said it is CustomerId need to be Input

    Thanks
    Parthi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply