SQL issue , How to insert multiple rows into two tables

[john96113]

Hey, I am a NEW comer, I try to write a ASP with DB-MS-Access, I encounter trouble,

Table 1 (order table)

OrderNo, CustomerNo.
1 011
2 033
3 023

Table 2 ( DetailOrder)

OrderNo, ItemNo, itemDescription
: : :
3 111 car
3 112 truck

-- For example customer 023 order 111 and 112 in OrderNo 3
Please help me how to write this SQL to put data order 3 in to both tables at same time with INSERT, Thank you very much.

 

[TheBugSlayer]

I don't understand the question. Which are the two tables you are talking about?

The same INSERT statement will add ALL records that meet your criteria into the table of your interest. Please explain.

 

[SQLSister]

This is not an Access forum, if that's what you are using suggest you take your question there.

In SQL Server you would do this in a stored procedure and do the first insert, use the scope_identity() funtion to get the value of the order no (which I am assuming is a field whic autoincrements) and set it equal to a variable. Then you would do the second insert referencing the variable as part of it.

This works well if you are inserting one record at a time.

If you must insert multiple records at a time, the situation becomes much more complex.

 

[john96113]

SQL sister is more close to my need, May I need alittle bit detail ? Maybe my question is not clear.. My question is when I click order button, for

Order No (3 ) CustomerNo (023)

will INSERT INTO the Table ORDER,

Then how could I write a SQL to pass (insert into) this Order No 3 to Detail TABLE with 2 orders like this

OrderNo, ItemNo, itemDescription
3 111 car
3 112 truck
Thanks.
John