Insert table records into another table

[hewstone999]

I have this query below:

INSERT INTO TEST_DOC
SELECT *
FROM MPI_ADDSS_IFF;

I currently have 10 tables in my database (All with the same colunm names and formats) however i want to click a button that will put all the tables into TEST_DOC, instead of doing it one by one. To do this this i would like some code either in SQL or VBA that will do this.

Any ideas or help?

Kind Regards
Hewstone999

 

[gemma-the-husky]

INSERT INTO TEST_DOC
SELECT *
FROM MPI_ADDSS_IFF;

assuming your table to copy from has something in the name you can identify in it (such as eg mpi_)

then do this - this reads all your tables, but selects certain ones only

dim mydb as database
dim tdf as tabledef

set mydb = currentdb

for each tdf in mydb.tabledefs
    if instr(tdf.name,"MPI_")>0 then
       docmd.runsql "INSERT INTO TEST_DOC SELECT * FROM " & tdf.name
    end if
next tdf

set mydb= nothing

 

[hewstone999]

INSERT INTO TEST_DOC
SELECT *
FROM MPI_ADDSS_IFF;

assuming your table to copy from has something in the name you can identify in it (such as eg mpi_)

then do this - this reads all your tables, but selects certain ones only

dim mydb as database
dim tdf as tabledef

set mydb = currentdb

for each tdf in mydb.tabledefs
    if instr(tdf.name,"MPI_")>0 then
       docmd.runsql "INSERT INTO TEST_DOC SELECT * FROM " & tdf.name
    end if
next tdf

set mydb= nothing

Thanks for the code. It works fantastic.
Can i limit the tables I put into it? i.e. I have a table call MPI_ADDSS_IFF and MPI_ADDSS_IFF_ChangeLog, how can i change it so i only have MPI_ADDSS_IFF?

 

[gemma-the-husky]

if instr(tdf.name,"MPI_")>0 then

this line tests the file name - change that to include/exclude tables as required. my example checks to see if a certain phrase is included inthe name

so

if instr(tdf.name,"MPI_")>0 and instr(tdf.name,"changelog")=0 then

 

[hewstone999]

OK thanks for ur help.

Here is a tricky one. When I insert the data into the table, i want to insert another colunm that will hold the table name where the record came from for each record. This data isn't sorted so it will have to get the information from the table name.

example

IDS USERNAME FORNAME TABLENAME
-----------------------------------------------------------------
1 example1 example1 MPI_ADDSS_IFF
2 example2 example2 MPI_ADDSS_IFF
3 example3 example3 MPI_IDS_IFF

...........

Any help or ideas?

 

[gemma-the-husky]

then yoo need to add a column to the destination table to be populated, and change the sql to add the table name in

create a query that does this in design panel, and see what the sql looks like. I think the sql will now become more complicated though, as yuo may need to specifiy every field separately