SELECT, INSERT combination : can be faster or not?

[oodie]

Hi,

I have question about SELECT and INSERT combination. Suppose that I have these tables:
Type: Status: Order:
ID | Type ID | Status ID | Order
---+----- ---+------- ---+------
1 | A 1 | B 1 | C

File:
ID | Order_ID | Type_ID | Status_ID | Filename
---+----------+---------+-----------+---------
1 | 1 | 1 | 1 | abc.txt

and I want to insert a new record in File table. I know the type, status, and order.

Currently I make 3 queries, each to get Order_ID, Type_ID, Status_ID, then insert the new record. Is there any way faster than this? I know that I can use 'INSERT INTO table (col1, col2) SELECT col1, col2 FROM other_table'. But it only works if I can get all the inserted columns from other tables. Here, I gat the Filename field from other process which doesn't use database.

Can anyone help? Thank's in advance

 

[fluteplr]

Your insert can include a variable. So if you have the filename you can do.


'INSERT INTO table (col1, col2) SELECT col1, m.filename FROM other_table'

 

[fluteplr]

Sorry about the last answer, I got confused as to which forum I was in.

 

[oodie]

It's OK. Thank's anyway The problem is that I don't get the filename from other table, so I can't use SELECT for it. Anyone can help?

 

[fluteplr]

Where do you get the filename from. Can you do this as a stored procedure where you could save the filename in a variable as in something like this.

declare @filename varchar(50)

select @filename = select filename from somewhere

INSERT INTO table (col1, col2) SELECT col1, @filename FROM other_table'