insert multiple values

Question

Post reply

insert multiple values

delwarinc

SSC Enthusiast

Points: 105
More actions
June 17, 2010 at 7:19 am

#387178

i want to insert muiltiple values in a table
table name - recipe_ingr
recipeID ingredients
12 a
12 b
12 c
12 d
now i have another recipe whose id is 72 and have same igredients as 12. therefore i want to insert new recipe id 72 which have same ingredents as 12.
so the new table will look like this
recipeID ingredients
12 a
12 b
12 c
12 d
72 a
----
-----
please can you tell me how to proceed

Viewing 10 posts - 1 through 10 (of 10 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

This looks too simplistic to be a real world problem.

Step by step:

Write a query to retrieve the ingredient rows of the recipe which you are cloning.

Hard-code the new RecipeID into the SELECT statement, overwriting the existing RecipeID.

Add the INSERT part to the SELECT query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

delwarinc SSC Enthusiast Points: 105 More actions · Answer 2

thanks for your reply, please can you show how to do this

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

delwarinc (6/17/2010)
thanks for your reply, please can you show how to do this

Of course!

You will have to write this bit though - Write a query to retrieve the ingredient rows of the recipe which you are cloning - because it will provide table and column names to use in the rest of the statement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

delwarinc SSC Enthusiast Points: 105 More actions · Answer 4

i found the solution using cursor

declare @ingred char(20)

declare mycursor cursor

local fast_forward

for

select ingredien from resipe_table where recipeid=(value of recipeID)

open mycursor

fetch next from mycursor

into @ingred

while @@fetch_status=0

begin

insert into recipeid

values(7,@ingred)

fetch next from mycursor

into @ingre

end

close c1

deallocate c1

David Webb-CDS SSCoach Points: 17398 More actions · Answer 5

A cursor-based solution will almost always be the worst way to accomplish anything in SQL Server. The select you used to build the cursor is almost enough to satisfy the request Chris made. You just need to add the id you're hard coding in the insert (7). Then, as Chris stated, you just need to wrap the insert around the select. It's a worthwhile exercise to complete, otherwise you're going to be stuck in a mindset that will not serve you well in your future career.

And then again, I might be wrong ...
David Webb

delwarinc SSC Enthusiast Points: 105 More actions · Answer 6

david and chris thanks for the info

and i agree that cursor 30 times less slow.

but to be honest i did not understant what chris is trying to say

"Hard-code the new RecipeID into the SELECT statement, overwriting the existing RecipeID.

Add the INSERT part to the SELECT query."

@chris-2 query to retrieve the ingredient

select ingredient from recipe_ingr where recipeID=12.

please could you help me to write the next step

thank you guys for assistance and explanation

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Query to retrieve the existing rows which we want to clone:

SELECT recipeID, ingredient

FROM recipe_ingr

WHERE recipeID = 12

Hard-code a new recipeID:

SELECT recipeID = 20, ingredient

FROM recipe_ingr

WHERE recipeID = 12

Add the INSERT part to the query:

INSERT INTO recipe_ingr (recipeID, ingredient )

SELECT recipeID = 20, ingredient

FROM recipe_ingr

WHERE recipeID = 12

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

delwarinc SSC Enthusiast Points: 105 More actions · Answer 8

thank you thank you, now i understand what u mean by hard code and insert. thanks for the explanation

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

You're welcome.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden