This is how I look at the problem. (I do all this using ssmse):
USE [cpqaAnswers]
GO
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_UM_dt3](
[MA] nvarchar(11),
[QTY] float(2)
)
BULK INSERT [cpqaAnswers].[cpqa].[tbl_UM_dt3] FROM 'C:\UM\dt3.txt'
Then I do some inspection:
SELECT * FROM [cpqaAnswers].[cpqa].[tbl_UM_dt3]
Which yields:
MA QTY
~~~~~~~~~~~~~~
MA00631 288
MA00631 240
MA00631 470.4
MA00959 1836
MA00959 1108.8
MA00959 1285.2
MA01070 2560
MA01070 1280
MA06195 989.4
MA06210 510
And then there's a second table, so I need that also:
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_UM_dt4](
[MA] nvarchar(11),
[STOCK] nvarchar(23)
)
BULK INSERT [cpqaAnswers].[cpqa].[tbl_UM_dt4] FROM 'C:\UM\dt4.txt'
I run a query to check whether the operation has occured. Sometimes BULK INSERT from file can be thwarted by poor format of the textfile.
SELECT * FROM [cpqaAnswers].[cpqa].[tbl_UM_dt4]
MA STOCK
~~~~~~~~~~~~~
MA00631 19194
MA00631 19194
MA00631 19194
MA00959 34589
MA00959 34589
MA00959 34589
MA01070 72431
MA01070 72431
MA06195 11073
MA06210 17220
So far so good. Now, this is where I'm drawing the line on your post. First of all something's not quite right about the data in these two tables. But anyway, since you speak of this quantity 100x, I think to myself "he's running through a nest of loops not just one loop". Here is what I see in my mind's eye:
SELECT DISTINCT U.[MA], U.[QTY], M.[STOCK] FROM [cpqaAnswers].[cpqa].[tbl_UM_dt3] AS U
JOIN[cpqaAnswers].[cpqa].[tbl_UM_dt4] AS M
ON(U.[MA]=M.[MA])
Running this query will result in:
MA QTY STOCK
~~~~~~~~~~~~~~~~~~~~~~~~~~
MA00631 240 19194
MA00631 288 19194
MA00631 470.4 19194
MA00959 1108.8 34589
MA00959 1285.2 34589
MA00959 1836 34589
MA01070 1280 72431
MA01070 2560 72431
MA06195 989.4 11073
MA06210 510 17220
Does it help to know that there's a way in TSQL to do what you want to do
using vbnet frontend? I think it's best to layer the database access with TSQL. Then serve up the data itself using an interface such as vbnet.
Hope this helps.