Best way to add multiple items from an order to a table

Question

Best way to add multiple items from an order to a table

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

When I add a request to the TB_PEDIDOS table,

TB_PEDIDOS:
ID_PEDIDO, DATA_PEDIDO, ID_CLIENTE

I also need to add the order details in 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

Do I add the details with a loop, or is there another way to do this?

The database is SQL SERVER.

sql-server c# asp.net

asked by anonymous 15.04.2014 / 03:36

2 answers

Data-Tables misaligning table title and body when scrolling JPQL to query date / time of bank with hibernate without native query

score 1 · Answer 1

Use Table Value Constructor for SQL Server 2008 or higher

link

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

score 0 · Answer 2

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 only as examples (places 0 to 100 products and 0 to 1000 different prices).