Insert With A Join?
I'm not sure how this might work, but I'm hoping it's possible...
I'm using VB.Net to read a file to pick up data to insert into a table. One of the values in the file is the store name which I need to cross reference to another table to get the store number. Now, I could (via VB code) use the store name to open the Store table and retrieve the store number which I can then put into the store ID field in the data table I'm inserting into, but I'm hoping that I can do it in one shot with an INSERT query.
The table I'm INSERTing into is "DataTable" and contains the fields "StoreID", "Manager", "SalesDate", and "SalesAmount"
The cross reference table is "Stores" and contains the fields "StoreID" and "StoreName".
The file I'm reading would contain what will be he fields of "StoreName", "Manager", "SalesDate", and "SalesAmount".
Is this possible, and how might I do it?
TIA
I'm using VB.Net to read a file to pick up data to insert into a table. One of the values in the file is the store name which I need to cross reference to another table to get the store number. Now, I could (via VB code) use the store name to open the Store table and retrieve the store number which I can then put into the store ID field in the data table I'm inserting into, but I'm hoping that I can do it in one shot with an INSERT query.
The table I'm INSERTing into is "DataTable" and contains the fields "StoreID", "Manager", "SalesDate", and "SalesAmount"
The cross reference table is "Stores" and contains the fields "StoreID" and "StoreName".
The file I'm reading would contain what will be he fields of "StoreName", "Manager", "SalesDate", and "SalesAmount".
Is this possible, and how might I do it?
TIA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not being familiar with VB.net, I was hoping that the second example would be more helpful.
I assuming you can pull the values out of the structure and have them stored in some variable.
If you're using a standard SQL insert statement, you should be able to use the second example and replace anything I called xxxxVariable with your structure variables.
(Admittedly I edited that in a few minutes after my original reply, so you may not have seen it when you started replying, based on the timestamps)
I assuming you can pull the values out of the structure and have them stored in some variable.
If you're using a standard SQL insert statement, you should be able to use the second example and replace anything I called xxxxVariable with your structure variables.
(Admittedly I edited that in a few minutes after my original reply, so you may not have seen it when you started replying, based on the timestamps)
ASKER
Did you modify your post? I somehow missed the second example, which seems to work (after I modified it to suit my particular situation).
Thanks,
Clif
Thanks,
Clif
No problem - yeah, I had edited in the second example after the initial reply. I'm thinking that my edit happened while your reply was in process.
Glad it worked for you!
Glad it worked for you!
ASKER
I'm writing code in VB.Net to read a series of text files. The values I need from those files are stored in an array (of structure). I'm then INSERTing the array values (through VB.Net code) to the DataTable table. Unfortunately one of the values from the text files is "StoreName" which I need to convert to "StoreID" (a value that I can get from the "Stores" table).
In short, the source data is not in an existing table, it's in an array. (No "MainTable" as in your example)