how to make a table with 10 million rows in one column?

[pemt]

how to make a table with 10 million rows in one column?

Num
1
2
3
..
..
..
10,000,000

thanks,

pemt

 

[KARL DEWEY]

WHY!

 

[John W. Vinson]

how to make a table with 10 million rows in one column?

Num
1
2
3
.
.
.
10,000,000

thanks,

pemt

Start with a table NUM with 1000 rows, with one field N, values 0 through 999.
You can use Excel... Insert... Fill Series to create it quickly, and copy and
paste into a table. Then create a Cartesian join query

INSERT INTO bigtable(Num)
SELECT 1 + N1 + 1000 * N2 + 1000000 * N3
FROM Num AS N1, Num AS N2, Num AS N3
WHERE N3 < 10;

Lots of other ways to do it but this should be quick and easy.

Compact the database when you're done...

 

[Jeff Boyce]

?Simple or harder?

Simple: Open Excel. Start numbering (down) 1 (down) 2 (down) 3. Highlight
these and extend as far as you can go.
Copy that into Access.
Repeat, starting at next number, until you reach 10M.

Harder: Create a procedure in Access that inserts a record/number, adds
one, inserts (another), repeats until 10M.

Now, why? What will having a table with 10M rows numbered 1 - 10,000,000
allow you to do?

(I ask because there may be a MUCH simpler way to accomplish what you want,
if we only knew what that was...)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Douglas J. Steele]

Just a note that you can only do the simple approach if you're using Excel
2007 (or newer)

 

[kc-mass]

Try this: Set it off and wait two or three minutes depending on your
machine.

Sub MakeBig()
Dim strSQL1 As String
Dim strSQL2 As String
Dim lngCount As Long
On Error GoTo E_Handle
strSQL1 = "INSERT INTO tblBig2 ( [key] ) SELECT tblBig1.key FROM tblBig1;"
strSQL2 = "INSERT INTO tblBig1 ( [key] ) SELECT tblBig2.key FROM tblBig2;"
DoCmd.SetWarnings False
Do While lngCount < 1000
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
lngCount = lngCount + 1
Loop
DoCmd.SetWarnings True
sExit:
On Error Resume Next
DoCmd.SetWarnings True
Exit Sub
E_Handle:

MsgBox Err.Description, vbOKOnly, Err.Number
Resume sExit
End Sub

 

[pemt]

John,

Thanks for your help.
When I run the query you wrote, it asked me to give input of N1, N2 and N3.
I gave N1=1, N2=2, N3=3, then it said "could not find output table 'bigtable'.
how to solve it?

pemt

 

[John W. Vinson]

John,

Thanks for your help.
When I run the query you wrote, it asked me to give input of N1, N2 and N3.
I gave N1=1, N2=2, N3=3, then it said "could not find output table 'bigtable'.
how to solve it?

Create your big 10,000,000 row table, empty; I called it "bigtable" in my
code, you should use whatever name you have chosen for your table.

And I did have a typo: the query should be

INSERT INTO bigtable(Num)
SELECT 1 + N1.N + 1000 * N2.N + 1000000 * N3.N
FROM Num AS N1, Num AS N2, Num AS N3
WHERE N3.N < 10;

 

[pemt]

John,

Thanks.
How to run below code in Access to make table?
n=1
n=n+1
n<10000000
print n /
end

pemt

 

[pemt]

Hi Karl,

actually I have more than 10 M records to fill with and each one is unique.

pemt

 

[pemt]

Jeff,

Thanks.
Actually I have more than 10 M records to fill with and each one is unique.

pemt

 

[pemt]

Jeff,

Thanks.
Actually I have more than 10 M records to fill with and each one is unique.

pemt

 

[John W. Vinson]

actually I have more than 10 M records to fill with and each one is unique.

THEN STOP!!!!!

It is not necessary to create ten million empty records and then fill them in.

Simply use an Autonumber ID. Add the records as the data becomes available -
importing, appending, entering with a form. Create the record *when there is a
record to create* - not before.

 

[John W. Vinson]

John,

Thanks.
How to run below code in Access to make table?
n=1
n=n+1
n<10000000
print n /
end

You *really really don't want to do that*.

See my other reply.

 

[pemt]

Thanks a lot!

THEN STOP!!!!!

It is not necessary to create ten million empty records and then fill them in.

Simply use an Autonumber ID. Add the records as the data becomes available -
importing, appending, entering with a form. Create the record *when there is a
record to create* - not before.

 

[Jeff Boyce]

Thanks, John. Clearly I wasn't getting through <G!>

Jeff B.

 

[John W. Vinson]

Thanks a lot!

I'm just really curious... what kind of data do you have, and where is it
coming from? If it's ok to post a general description several of us would be
interested.

 

[pemt]

kc-mass,

I tried to run this code, but the error came out "cannot find input table or
query 'tblBig1'."

Try this: Set it off and wait two or three minutes depending on your
machine.

Sub MakeBig()
Dim strSQL1 As String
Dim strSQL2 As String
Dim lngCount As Long
On Error GoTo E_Handle
strSQL1 = "INSERT INTO tblBig2 ( [key] ) SELECT tblBig1.key FROM tblBig1;"
strSQL2 = "INSERT INTO tblBig1 ( [key] ) SELECT tblBig2.key FROM tblBig2;"
DoCmd.SetWarnings False
Do While lngCount < 1000
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
lngCount = lngCount + 1
Loop
DoCmd.SetWarnings True
sExit:
On Error Resume Next
DoCmd.SetWarnings True
Exit Sub
E_Handle:

MsgBox Err.Description, vbOKOnly, Err.Number
Resume sExit
End Sub

how to make a table with 10 million rows in one column?

Num
1
2
3
.
.
.
10,000,000

thanks,

pemt

.

 

[pemt]

Hi John and others,

sorry for the late response.
The data are from bioinformatic data. Actaully I am a biologist. Now data
analysis took really long long time for those bioinformaticists as compared
with our wet bench work. Thus, I try to find a simple way/interface for
biologists to analyze their own data from large dataset. Access can be easily
handled by many computational dummies like me, though Access might not be
good for very large datasets. But with you guys' great help, it worths to
try. And it works for me to analyze some relatively small datasets now. SQL
might be a better choice. Is there any way I will be able to process SQL
database from Access interface, like running queries ...?

Thanks a lot,

pemt

 

[John W. Vinson]

Is there any way I will be able to process SQL
database from Access interface, like running queries ...?

Absolutely. SQL makes a great backend to Access, using ODBC to link the SQL
data.

Having worked with some bioinformatics folks in the past... have you looked at
SAS or SPSS? Cheap, no; easy to configure, no; but lots of background in the
subject, certainly.