Let's try this again so I can get some help

[tmcrouse]

I run a monthly query for data and am tired of the query taking 4 hours cuz i have to use the new raw data I get each month. I have been just running an update query to get both primary keys in the two tables the same length. one table has over 10 million subscribers thus the reason it takes 4 hours to run the update query. I am trying to find a faster way rather than doing this update every single month. Here is the information.



I have two tables with multiple columns and the primary key is the same but not the same length. 1 is only 8000 rows and the other 10.7 million. I am trying to take the 8000 and compare it to the 10.7 million and get all the data from the 10.7 million based on the primary key in the 8000 row table. The key looks like this:

table1 table2

CustNbr1 CustNbr2
00800900 0800900
00810900 0810900
00811900 0811900
00912480 3912480

I have tried to do a join query and the query looks like this and it comes up with no results every time.

select right([custnbr1],6) as expr1, right([custnbr2],6) as expr2, table2.subid, table2.ind from table1 inner join table2 on table1.custnbr1=table2.custnbr2;

Now remember I typed this by looking at my work laptop and just manually typing so I can't copy and paste and if I missed some () or [] I apologize. The SQL is right in my work laptop. I just can't go to sites on my work laptop like this. They are not allowed.

[tmcrouse]

Someone said in my other post to import the data dropping that first digit. I think that is the simplest way. I will have to see how to do that with UltraEdit. I don't know how. That is the tool they gave me to chop up data.

[ajetrumpet]

I'm confused crouse...so you want to UPDATE the appropriate fields in the table with 8000 rows with the DATA from the table with 10.7 million rows, based on matching values in the PK fields. Is that correct?

[weekend00]

I think he just want a select, not update.

[pbaldy]

Your SQL won't return records because the join fields aren't equal. Try this shot-in-the-dark, which will have to be done in SQL view:

select right([custnbr1],6) as expr1, right([custnbr2],6) as expr2, table2.subid, table2.ind from table1 inner join table2 on Right(table1.custnbr1, 6) = Right(table2.custnbr2, 6)

With that many records, you may want to add a WHERE clause while testing; it might be slow.

[weekend00]

He said it took only 30 seconds to run! Do you believe?

[ajetrumpet]

Your SQL won't return records because the join fields aren't equal.

Good eye Paul.

[pbaldy]

Good eye Paul.

Grazie!

I do believe 30 seconds, but having functions in the join will likely slow down the comparison and thus the query. If the data is in SQL Server or something, you could also create a View that returned the appropriate length key field, and base your queries on that. That would force the conversion work to be done by the server.