Looping through a table SQL

Question

Okay so I have this temp table. It has all the orders which a company needs to ship out. I need to somehow loop through the table and insert the information into 3+ tables.

@TempTable Table
(
    OrderID Int
)

Declare @value int = (select count(orderID) from @temptable)
Declare @i int = 1
WHILE @i < @value BEGIN
    Declare @orderid= (select first(orderid) from @temptable)
    INSERT INTO shipment (orderid, Price, Date, DateDue)
    VALUES (@orderid, @Price, @Date, @DateDue);
    Set @i += 1
    Delete top(1) from @temptable
END

Is there a better way of doing this?

Adding a little more to my issue

I'm taking in 3 values from VB.Net that as an example is @Price, @Date, and @DateDue.Because of this I wasn't able just to do a select statement cause the values are mixed with this passed values.

possible duplicate of [SQL Insert into ... values ( SELECT ... FROM ... )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) — APH, Sep 22 '15 at 19:09
Not only is it inefficient it is also potentially inaccurate. You have a delete top 1 but you don't have an order by. You also are using first but the function in sql server is first_value. — Sean Lange, Sep 22 '15 at 19:13
@blackpluribus After your add some info this become more difficult to understand what you want. I suggest you read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Sep 22 '15 at 19:43

score 2 · Answer 1 · answered Sep 22 '15 at 19:09

2

Do it in a single query

INSERT INTO (orderid, -some other value-)
   SELECT orderid, -some other value-
   FROM @temptable

answered Sep 22 '15 at 19:09

Juan Carlos Oropeza

47,252
12
78
118

How the fastest plus 1 i ever got – Juan Carlos Oropeza Sep 22 '15 at 19:10
Does this work if the other values are passed from code? – blackpluribus Sep 22 '15 at 19:16
Probably yes. But need see a more detail description of what you mean. – Juan Carlos Oropeza Sep 22 '15 at 19:17
Example : INSERT INTO (orderid, price) SELECT orderid, @price FROM #temptable – blackpluribus Sep 22 '15 at 19:21
That will insert same price to all `temptable` rows. is that what you want? – Juan Carlos Oropeza Sep 22 '15 at 19:22
I was trying to just pull out the OrderIDs each time to pass it along with 3 other values from outside the table into another preexisting table. – blackpluribus Sep 22 '15 at 19:28
Yes, that lose me :( I suggest you rewrite the question and explain all the issues regarding the problem. Include some sample date and desire output. – Juan Carlos Oropeza Sep 22 '15 at 19:44

Olivier Jacot-Descombes · Accepted Answer · 2015-09-22T19:50:01.547

0

Looping is not efficient. Always try to avoid it. You will need two queries: one for selection and inserting and one for deletion

INSERT INTO shipment (orderid, Price, Date, DateDue)
SELECT orderid, @Price, @Date, @DateDue FROM @temptable;

DELETE FROM @temptable;

Note also that the @temptable has a limited lifetime. Therefore - depending on the situation - deleting it might not be necessary at all.

edited Sep 22 '15 at 19:50

answered Sep 22 '15 at 19:15

Olivier Jacot-Descombes

104,806
13
138
188

Normally I would agree with you but my issue is the other values aren't from the temptable so I can't just select from the temptable. – blackpluribus Sep 22 '15 at 19:43
Well, if those other values are pararamters, then use parameters. I changed the query accordingly. – Olivier Jacot-Descombes Sep 22 '15 at 19:51

Looping through a table SQL

2 Answers2