copy data from multiple tables with SQL

[HarrySpencer]

hi, i've found some code to copy info from one table to another but need to change it so that it will copy data from multiple tables, certain fields only, to a single table. it is a sort of archive function but won't be used very often so didn't think it was necessary to create 1 to many relationships for a load of tables. let me know if you can think of a better way or solution to this, here is the code i found about the forum...

Dim strSQL As String

    strSQL = "INSERT INTO PasteHere_tb([CopyID], [PasteName], [PasteDate], [PasteComments]) "
    strSQL = strSQL & "SELECT CopyThis_tb.[CopyID], CopyThis_tb.[CopyName], CopyThis_tb.[CopyDate], CopyThis_tb.[CopyComments]"
    strSQL = strSQL & "FROM [CopyThis_tb]"
    strSQL = strSQL & "WHERE (((CopyThis_tb.[CopyID])=[Forms]![CopyThis_tb]![CopyID]));"

    DoCmd.RunSQL strSQL

is it possible to just run a variation for each table i want to copy FROM or will the INSERT INTO create a new record.

 

[The_Doc_Man]

Not clear on your question.

If you want to write a single record that contains data from multiple related records, there are several scenarios.

1. If the tables are related and one//one, JOIN them first in a separate query and then do the insert of a single record that is the result of two records joined by the separate SELECT with JOIN.

You will have as many records as the number of records in the smaller table if you us INNER JOIN or as many records as the number of records in the larger table if you use OUTER JOIN. If relational integrity is turned on, then both tables will be the same size and you will get as many records as are in either table.

2. If the tables are related but one//many, this only makes sense if the joined records are translations of fields in the central table. I.e. if you have state codes in the main table but wanted from some contrived reason to store the spelled-out state names, you could join the table to the translation table and store that. OR you could write a query that does a DLookup for each translated field and store the query to your archive table.

If the things you want AREN'T lookups, you will store as many records as are in the MANY side of the relationship. If relational integrity is NOT turned on, however, the table on the many side of the relationship can in theory have records not corresponding to records in the one side, and therefore in a JOIN query, some of those entries would be skipped.

3. If the multiple tables being stored are many//many through a linking table, a multiple JOIN is possible if executed in stages. Suppose you have two tables A and C, and both have a one//many relation with B, their linking table. If query 1 joins A to B and then query 2 joins query 1 to C, store selections from query 2.

Doing it this way, you will store as many records as are in B. Again, if relational integrity is turned off, some records (this time from A and C) can be skipped if table B doesn't reference them.

4. All that is left is unrelated tables. If you wanted to store a single record based on two unrelated tables, you run a massive risk of a permutation join, which is the number of records in A TIMES the number of records in B. Not to mention that this type of join would violate normalization. The record so generated cannot have a proper candidate key since for all records, at least some of the fields would not be based on the key.

 

[modest]

Explain what you're trying to do. Say you have a table [Dog].

Dog:
Height | Weight | Color | Breed

Does the one table you wanna copy from have the height/weight information and the other have color/breed info? (Are you trying to combine the data on the same row/record?) Or, does one table have information on dogs from two different time periods, retrieving height,weight,color, and breed from both tables into new records of the new table, Dog.

For the first problem, you would need an INSERT statement like the one you listed above, and then an UPDATE query to update particular data of the table, or if you would rather not use an update, you may use a JOIN inside the INSERT statement.

For the second problem (the one I think that was implied since you mentioned an archive), you may just use the INSERT statement. Each import from the table will be placed as a new record (a new line in the table).

Syntax:
INSERT INTO [Destination Table Name]([Field 1],[Field 2],[Field 3],[etc])
SELECT [Field 1], [Field 2], [Field 3], [etc]
FROM [Import Table Name ]


You would have two of these for each table you're importing.