February 9, 2011 at 8:48 am
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
February 9, 2011 at 8:52 am
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
February 9, 2011 at 8:59 am
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
February 9, 2011 at 9:01 am
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/
February 9, 2011 at 10:29 am
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