Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert With A Join?

Posted on 2013-01-30
5
Medium Priority
?
149 Views
Last Modified: 2013-01-30
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
0
Comment
Question by:Clif
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 38835154
This should do the trick (if it were all table based):

insert into DataTable (StoreID, Manager, SalesDate, SalesAmount)
select b.StoreID, a.Manager, a.SalesDate, a.SalesAmount
from MainTable a, LookupTable b
where a.StoreName = b.StoreName

Open in new window


Just watch things like case sensitivity on the name column etc - Joining on a text column can have its own set of issues.

Since you're reading from a file and dealing with what I'm assuming are program variables, replace references to a.column with variable names

insert into DataTable (StoreID, Manager, SalesDate, SalesAmount)
select b.StoreID, ManagerVariable, SalesDateVariable, SalesAmountVariable
from LookupTable b
where b.StoreName = StoreNameVariable

Open in new window

0
 
LVL 10

Author Comment

by:Clif
ID: 38835192
I'm sorry, but I don't think I explained the source data very well.

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)
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38835719
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)
0
 
LVL 10

Author Closing Comment

by:Clif
ID: 38835750
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
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38836216
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!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question