0

Hi I am building an order intake form that will have information submitted belonging to 3 tables. The order table will have the primary key used for the property and customer table.

How can I simultaneously create the primary key via the identity on the order table and insert that into the property and customer table?

SQL please.

Order OrderKey PK

Property OrderKey FK

5

If you insert multiple Order records (pick a new name that's not a keyword, by the way) then you need to use the OUTPUT clause. Otherwise SCOPE_IDENTITY() should work.

Quick example using SCOPE_IDENTITY:

DECLARE @OrderID int

INSERT INTO
  Orders
   (<columns>
VALUES
 (....)

SET @OrderID = SCOPE_IDENTITY()

INSERT INTO
  Properties
   (OrderID, ...)
VALUES
  (@OrderID, ...)
  • i'll try this. Thanks it's actually orders (plural) for that reason – Jayzihno Feb 12 '15 at 19:09
  • It worked. Thanks for the quick response. I used this in a SP and wasn't sure about what to put in the parameter for @OrderID so i left it blank. It still populated to my table though. – Jayzihno Feb 12 '15 at 19:19
  • In the SP you would declare @ORDERID as a variable internal to the SP, not as a param, and use it just like I listed above. – JNK Feb 12 '15 at 19:24
  • @Jayzihno It should be that way not because it conflicts with a keyword but because the table is a set of orders, not a set of order. IMHO. If you only ever had one order (or were only ever dealing with a single order at a time), there wouldn't be much point of having a table. – Aaron Bertrand Feb 12 '15 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.