How to copy a record from one table to another same table, if I only know the record no

[joyo]

Hi,

I have a data table which contains big dataset. I want randomly pick
up some records and save them to another table. The two tables have
same structures and fields. First, I generate some random numbers. My
question is, how to use these numbers to find the certain records and
save these records to a new table. The big table doesn't have an id
field, such as autonumber field or RecordID field. I get this table
from external database or Excel file using "TransferDatabase" or
"TransferSpreadsheet" functions. If I have to insert an ID field in
this table. How to do that?

Thanks

joyo

 

[Marshall Barton]

I have a data table which contains big dataset. I want randomly pick
up some records and save them to another table. The two tables have
same structures and fields. First, I generate some random numbers. My
question is, how to use these numbers to find the certain records and
save these records to a new table. The big table doesn't have an id
field, such as autonumber field or RecordID field. I get this table
from external database or Excel file using "TransferDatabase" or
"TransferSpreadsheet" functions. If I have to insert an ID field in
this table.

Here's some air code with a slightly tricky way to do
something like what you want:

strSQL = "INSERT INTO tbl1 " _
"SELECT TOP 100 * FROM tbl2 ORDER BY Rnd(somefield)"
Randomize
db.Execute strSQL

 

[Guest]

Thank you for your help. Please see the following SQL
statements:

insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 1
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 2
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 3
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 4
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 5

Should I get the first 5 records?

Thanks

joyo

 

[Marshall Barton]

Thank you for your help. Please see the following SQL
statements:

insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 1
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 2
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 3
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 4
insert into tbl_SampleData select top 1 * from
tbl_OrginalData order by 5

Should I get the first 5 records?

I don't think so, using a number in the OrderBy clause tells
the query to sort on the field with that index into the
fields list. Even if it didn't do that, you would be
sorting be a constant, which would effectively not sort
anything.
--
Marsh
MVP [MS Access]

 

[Jamie Collins]

(e-mail address removed) (joyo) wrote ...

I want randomly pick
up some records and save them to another table. The two tables have
same structures and fields. First, I generate some random numbers. My
question is, how to use these numbers to find the certain records and
save these records to a new table. The big table doesn't have an id
field, such as autonumber field or RecordID field. I get this table
from external database or Excel file using "TransferDatabase" or
"TransferSpreadsheet" functions. If I have to insert an ID field in
this table. How to do that?

You could temporarily add an IDENTITY column then later drop it. Air SQL:

ALTER TABLE Mytable
ADD MyTempID IDENTITY(1,1)
;
SELECT *
INTO MyOtherTable
FROM Mytable
WHERE MyTempID IN (<<random number list>>)
;
ALTER TABLE Mytable
DROP MyTempID
;

Jamie.

--

 

[Jamie Collins]

Oops! That should be

INSERT INTO
(<<column list>>)
SELECT
(<<column list>>)
FROM Mytable
WHERE MyTempID IN (<<random number list>>)
;

--