0

I want to insert values into two tables.

The first one looks like this

Recipes

RecipeId (unique and AI)
RecipeName
Method
Discontinued

The second one looks like this

IngredientRecipe

RecipeUniqId (unique and AI)
RecipeId (same as the RecipeId in the Recipes table)
IngredientId

I want to insert a RecipeName and a Method. If I do that the RecipeId gets auto incremented. Than I want to insert three times an ingredient with the RecipeId. Its working for one ingredient.

INSERT INTO Recipes (RecipeName, Method, Discontinued)
OUTPUT INSERTED.RecipeId, '5'
INTO IngredientRecipe(
    RecipeId,
    IngredientId
)

VALUES ('Potato and Chips', 'Potato and Chips bake everything', 'false');

So what I want is that I can add three times the same RecipeId with a different number for the IngredientId.

How to do this? I use sql server

EDIT:

I use asp.net with c# and an N-tier structure. I use a sql server database

Niek Nijland
  • 732
  • 1
  • 7
  • 20
  • Do you add recipe one by one? – Serge May 21 '13 at 09:36
  • I add the recipe one by one but I want to add for each recipe 3 ingredients so when you add a recipe I need the RecipeId and than the ingredientId. Just as my example but I want to add three ingredientId's with the recipeId. – Niek Nijland May 21 '13 at 09:59
  • then I think Karel's answer might be the best solution for you. – Serge May 21 '13 at 10:05

1 Answers1

1

Declare a variable and store the value of the primary key used

declare @id int

Insert the data into Receipes table and use the SCOPE_IDENTITY function (see this for more)

 INSERT INTO Recipes (RecipeName, Method, Discontinued) VALUES (....)
 SELECT @id = SCOPE_IDENTITY

Repeatedly insert the ingredients using the value

INSERT INTO IngredientRecipe(RecipeId,  IngredientId) VALUES (@id, ...)
INSERT INTO IngredientRecipe(RecipeId,  IngredientId) VALUES (@id, ...)
...
Karel Frajták
  • 4,389
  • 1
  • 23
  • 34
  • Do I have to add all of these underneath each other in one big query? – Niek Nijland May 21 '13 at 09:58
  • You will have to generate it manualy in code and then execute. Please edit your answer so it will explain how this will be used - i.e. from app, what language, etc. – Karel Frajták May 21 '13 at 10:09
  • I use C# with asp.net and a sql server database – Niek Nijland May 21 '13 at 10:15
  • See http://www.codeproject.com/Articles/13419/SelectQueryBuilder-Building-complex-and-flexible-S or http://stackoverflow.com/questions/293254/creating-safe-sql-statements-as-strings for inspiration. But you can just use `StringBuilder`, `SqlCommand` and do not forget to use `SqlParameter`, otherwise you will spend ages with escaping values. – Karel Frajták May 21 '13 at 10:20