Thanks for that explanation bud. Consider my bare minimum of 300,000 "numbers" at a guestimate of 1 second per row, results in 83 hours... but will definitely put this to the test tonight
Please provide table 2.
If table 2 has 3 lines for customerID 1 then it should be a simple left outer join.
Hey Chistos
There is no data in Table2 bud. My problem is that I am trying to populate Table2 with data effectively. Example of how Table2 should look after a viable INSERT mechanism is written, is provided in the OP.
As explained to KimVapeDashian: Table1 holds a unique customer id and a count of items bought per customer id. I need to insert the customer id as many times as the quantity of items/entries as stated in Table1 into Table2 (which is on average about 22 times). The reason I cannot simply write individual INSERT statements is because Table1 holds 300k+ records.
Hope that makes sense.
Thanks
can you do:
select Entries from Table 1
group by entries
If your entries go from 1 to 20(I.E Not too many of these) I have an idea that will save you buttloads of time but requires a "setup" of a table.
I basically want to know how many different entries there are e.g. 1,2,3,4,5,6 so im eliminating the duplicates because im sure 3 will be duplicated about 1000 times etc.
Your headache is my bread and butter and vape gearMy head hurts
Good luck there @Cespian !
Hope you don't fry any components
I thought I had Excel issues - until I read this thread...
Might explain why my head hurts most days!
Sure... that ill have to do tomorrow. I only brought a 5k sample home. Thanks bud.
My head hurts
Good luck there @Cespian !
Hope you don't fry any components
I thought I had Excel issues - until I read this thread...
Haha @Silver as @Christos mentioned, its bread and butter for us. Im pretty sure I would be lost as hell in your area of expertise.
Just btw, if you require Excel help, please hijack this thread
@Cespian, the logical approach here will get you a query that runs for hours.
If
If
Should take a few seconds to try it on your test data provided the data is randomised and not the first 5k records
@Cespian, the logical approach here will get you a query that runs for hours.
If
Should take a few seconds to try it on your test data provided the data is randomised and not the first 5k records
It is the first 5000 records and ordered by entries ASC
so basically the numbers are ranging from 1 to 46. The only number missing in this sequence is 34
Sounds promising.
I'll give you assistance once junior is sleeping. Can't really do much without him jumping on me right now.
I'll give you assistance once junior is sleeping. Can't really do much without him jumping on me right now.
No urgency bud. Im leaving for the airport as soon as I get my juniors to bed as well. So will be out of commission until at least 11ish. Got until Friday next week to come up with a solution
Create a table with the entries I. E entry amount.
So it will look like
ENTRY AMOUNT
1
2
2
3
3
3
4
4
4
4
You can create a loop to do this for you.
Should run in under 5 mins to create the table.
Then do a left join in the table with Table1 on entry amount = client entries
This should give you what you are looking for and 300K records in a select join should take under a minute if not 10 seconds.
If you want help writing the query let me know.
I was about to report back here on a similar method
Worked in test sample space... Although my "loop" was just an excel macro to populate my number table... Well done @Christos
Btw here is my adapted excel macro, to replicate the numbers
Sub KimCopy()
Dim lRow As Long
Dim RepeatFactor As Variant
lRow = 1
Do While (Cells(lRow, "A") <> "")
RepeatFactor = Cells(lRow, "A")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
Range(Cells(lRow, "A"), Cells(lRow, "A")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "A")).Select
Selection.Insert Shift:=xlDown
lRow = lRow + RepeatFactor - 1
End If
lRow = lRow + 1
Loop
End Sub
Populating column A, with numbers 1 - 6 as a start
This is then the outcome of the macro data
Sub KimCopy()
Dim lRow As Long
Dim RepeatFactor As Variant
lRow = 1
Do While (Cells(lRow, "A") <> "")
RepeatFactor = Cells(lRow, "A")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
Range(Cells(lRow, "A"), Cells(lRow, "A")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "A")).Select
Selection.Insert Shift:=xlDown
lRow = lRow + RepeatFactor - 1
End If
lRow = lRow + 1
Loop
End Sub
Populating column A, with numbers 1 - 6 as a start
This is then the outcome of the macro data
If only I liked or knew how to use excel
I usually import excel into MSSQL and do everything from there.
I guess its just years of SQL experience that make TSQL more appealing and "simpler".
Hey guys,
Sorry for the late response... been stuck in meetings
sorry for my doffness, but how would I script the loop in SQL? I am very much familiar on how to do this in VBA and PHP... If I understand correctly:
1. Create "entry_amount" table
2. Insert each "entries" value into "entry_amount" table multiplied by itself. eg. If customer_id 1 has 3 entries, and customer_id 16 has 3 entries, the number 3 needs to appear 6 times in the "entry_amount" table
3. LEFT OUTER JOIN table1.entries on entry_amount.entries
The entries in Table1 are not unique, only the customer_id is unique. If the entries were unique, this would work seemlessly. Going to paint a clearer picture below:
Snippet of Table1 below (data not real but accurate representation):
As you can see, both Customer 1 and Customer 4 has 4 entries, and Customer 3 and Customer 5 has 2 entries each.
Even if I expand the entries to look like this:
And do a LEFT JOIN with TABLE 1, SQL will bring only the first found match, meaning that; if there were 10 customers with 4 entries, the first Customer will be selected for each unique entry. Whereas I need to see the following:
Please slap me if I am reading your suggestion incorrectly.
Thanks
Simple select from customer first and then left outer join to entries.
All of the customers will join on whatever matches.
I'll give you a loop example later.running out for a bit.
Over the years, I noticed that Excel is very misunderstood. First mistake is that people use it as a DB (I have honestly discovered one with 15 sheets, holding roughly 600K rows and at least 5 columns each)... It is honestly one of the most versatile tools I've ever discovered if used correctly. I do design work, presentations, change delimiters in txt files, schedule reports (to name a few) from Excel. I love it. Every single one of the objects below is created in Excel:
So yeah, Excel is Bae for me too!
I love what you have done there! Really nice use...
And, yes - Excel is a far more powerful tool than people understand (i still learn new things every day)... Ive also seen these "database" spreadsheets, and their slow executions. External connections, and clean queries really make the world of difference...
We need to have a coffee and vape!
Next time im in CT or you come up to JHB
Just saw location.
Just saw location.
Next time im in CT or you come up to JHB
Just saw location.
Haha was actually responding about location now.
Thanks bud, will let you know (rare probability though) if I come up that way again.
Similar threads
