Results 1 to 5 of 5
  1. #1
    BruceUK is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    48

    SQL "INSERT INTO" question

    I have two tables, tbl1 and tbl2, linked by a common primary key attribute. Each attribute of tbl2 is also in tbl1 with identical names and definitions, but tbl1 also contains other attributes not in tbl2.

    At one point in a procedure, I need to create new records in tbl2 from corresponding records in tbl1, based on matching their key values. Because both tables have such a large number of attributes, I was hoping to use the * shortcut in the SQL statement as follows:

    DoCmd.RunSQL ("INSERT INTO tbl2 SELECT * FROM tbl1 WHERE lngKeyValue = " & lngVBACurrentKeyValue)

    I thought this would just deal with those attributes that are common to both tables. But of course it's giving a run-time error when it hits an attribute in tbl1 that isn't defined in tbl2.

    I know I can replace the * with an explicit list of all the attribute names that are common to both tables, but this would make it a very long statement. So is there any other SQL statement that I can use that will just deal with the common attributes?


  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Let's take a step back for a minute.
    Why do you need to insert the records from one table into another? You usually don't want/shouldn't have duplicate records within a database.
    There may be a better way to accomoplish what you want, but we need to have a better understanding of what you are ultimately trying to accomplish here before we can give recommendations.

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Bruce!

    You can do something like that:
    Code:
    Dim db As DAO.Database
    Dim strFields As String
    
    Set db = CurrentDb
    'Build the field names.
    For Each fld In db.TableDefs(tbl2).Fields
        strFields = strFields & "[" & fld.Name & "], "
    Next fld
    'Trim the last ", "
    strFields = Left(strFields, Len(strFields) - 2)
    'Execute the query.
    db.Execute "INSERT INTO tbl2 (" & strFields & ") SELECT " & strFields & " FROM tbl1 WHERE lngKeyValue = " & lngVBACurrentKeyValue, dbFailOnError
    Set db = Nothing
    No matter how many fields are.

  4. #4
    BruceUK is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    48
    Many thanks for the replies. I decided to follow JoeM's advice and rethink what I was trying to do, and of course he was right - I didn't need the second table at all. I just needed a new query to achieve the same result.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!
    Glad you got it all sorted out.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 3
    Last Post: 09-21-2017, 05:40 AM
  3. Replies: 9
    Last Post: 02-02-2016, 06:27 AM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 2
    Last Post: 01-03-2014, 09:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums