Including select in transaction

[roypython]

Hello.

Your help is very much apperciated.
I am using SQL SERVER 2005, and framework 1.1.
I would like to create transaction, that:

inserts a record to one table1 -> selects the new record's PK(identity) ->
Inserts a record to table2, with FK (equals to the PK of table1).

Example:
Table1 (orders):
order ID (PK, identity)
Order Date

Table2(items ordered)
Item ID (PK)
OrderID (FK)


1. Insert a record to table1
2. Get the order ID (PK), of the new record, from table1.
3. Insert record into table2, using the order ID .

I don't know how to include SELECT in transactions.
Can it be done?

Thanks
Roy

[Freon22]

Table1 (orders):
order ID (PK, identity)
Order Date

Table2(items ordered)
Item ID (PK)
OrderID (FK)

1. Insert a record to table1
2. Get the order ID (PK), of the new record, from table1.
3. Insert record into table2, using the order ID .

I hope others will post to help you because I am new to this also. But to help you to get started. You can do it a few different ways the easyest would be with two inserts. The first one fills table 1 and returns the order_ID number, I am thinking that this order_ID is an auto fill field?

INSERT INTO table 1 (field1, field2, field2) VALUES (value1, value2, value3); SELECT Scope_Identity()
So anyways what the first one is you insert the values into table one and ask for a return of the PK auto fill field. The select Scope_Identity() will return it. You can then do another insert using that scope_identity.


INSERT INTO table_1 (identity) VALUES ('somevalue'); INSERT INTO table_2 (OrderID) VALUES (Scope_Identity())

In this one I did a insert into both tables with the same script. Run some test and try it.