Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    6

    Unanswered: insert rows to a table using another table

    Hello!

    I have a table with 2000 rows, where the first field is a article number.

    In another table I have 300 rows, also here is the first field article number.

    half of those in the small table is aldready in the big table. Where itīs not, I want to create new rows with the right article number.
    But only when itīs not allready excisting in the big table.

    I am using access 2013, can somebody please help me with a query for that?

    thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be something like (replace the names with those of your database):
    Code:
    INSERT INTO [BIG TABLE] ( [article number], [Field 2], ... )
    SELECT [article number], [Field 2], ...
      FROM [small table] LEFT JOIN
           [BIG TABLE] ON [small table].[article number] = [BIG TABLE].[article number]
     WHERE [BIG TABLE].[article number] IS NULL;
    Have a nice day!

  3. #3
    Join Date
    May 2013
    Posts
    6
    Hello!

    Thanks, but there is one problem!

    if I use tha last row you wrote (where ... is null) I get the message that he want insert any new rows, I remove that, it will insert the same number of rows as there is in the small table.

    I understand why, but is the "is null" right? dosenīt it just insert where the cells is empty?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you open a new query in SQL view and paste this into it (with the necessary replacements to match the actual names in your database):
    Code:
    SELECT [article number], [Field 2], ...
      FROM [small table] LEFT JOIN
           [BIG TABLE] ON [small table].[article number] = [BIG TABLE].[article number]
     WHERE [BIG TABLE].[article number] IS NULL;
    When you then switch to Datasheet view you'll see that it shows all rows that are in [small table] but not in [BIG TABLE] which, if I correctly understood your question, is what you're looking for.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •