Best way to add multiple order items to a table

Question

Best way to add multiple order items to a table

Asked 10 years, 6 months ago

Viewed 1,405 times

1

When I add a request to the table TB_PEDIDOS,

TB_PEDIDOS:
ID_PEDIDO, DATA_PEDIDO, ID_CLIENTE

I also need to add order details to another table:

TB_DETALHES_PEDIDOS:
ID_PEDIDO, ID_PRODUTO, QUANTIDADE, PRECO

Example:

Produto1 QTD:2 PRECO:5
Produto2 QTD:1 PRECO:2
Produto3 QTD:8 PRECO:3

Add the details with a loop, or is there another way to do this?

The database is SQL SERVER.

1

please improve the issue !!!

– user6026

2014/04/15 at 01:42
see if you can understand now..

– Gerson

2014/04/15 at 01:53
1

@Gerson, your question is a little confusing, but from what I understand you want to know if there is any way to insert data into 2 tables in the same insert, to add the order and items into a single insert? Is that it? Something like a insert in cascade?

– Fernando Leal

2014/04/15 at 02:25
you are using some Framework ?

– Marco Souza

2015/11/27 at 13:10

2 answers

Browser other questions tagged c# asp.net sql-server

You are not signed in. Login or sign up in order to post.

by Marc.Adans • **190** points · Answer 1 · 2014-04-17T13:37:37+00:00

Use Table Value Constructor, for SQL-Server 2008 or higher

http://technet.microsoft.com/en-us/library/dd776382.aspx

INSERT INTO TB_PEDIDOS (QTD, PRECO, ...)
  SELECT QTD, PRECO--, ...
    FROM ( VALUES (2, 5)
                , (1, 2)
                , (8, 3)         
         ) tPedidos (QTD, PRECO)

by Leonel Sanches da Silva • **88,623** points · Answer 2 · 2014-04-15T04:58:38+00:00

INSERT TB_DETALHES_PEDIDOS (ID_PEDIDO, ID_PRODUTO, QUANTIDADE, PRECO)
SELECT pe.ID_PEDIDO, pd.ID_PRODUTO, RAND() * 100, RAND() * 1000
FROM TB_PEDIDO pe
CROSS JOIN TB_PRODUTO pd

RAND() generates a number between 0 and 1. I put 100 for product and 1000 for price just as examples (puts from 0 to 100 products and from 0 to 1000 different prices).