INSERT INTO from another table

Results 1 to 2 of 2

Thread: INSERT INTO from another table

  1. #1
    Join Date
    Dec 1969

    Default INSERT INTO from another table

    I am trying to construct an INSERT INTO SQL command in MS Access 2000 that would take records from one table and add them to another, ONLY if that record doesn&#039;t already exist. Both tables have the same exact structure. The primary key may be duplicated, so I need to avoid copying over that field. This means that I need to specify which fields I&#039;m copying in. I tried the following query, but I was told that it would append 325,159 records, when there are only 314 records in the one file to start with!<BR><BR>INSERT INTO [Tech Ed Contacts] (L_Name, F_Name, Address1,Address2,City,State,Zip,ftea,[E-Mail],Work_Phone,Home_Phone)<BR>SELECT W.L_Name, W.F_Name, W.Address1, W.Address2, W.City, W.State, W.Zip, W.ftea, W.[E-mail], W.Work_Phone, W.Home_Phone<BR>FROM WebMembers W, [Tech Ed Contacts] T WHERE T.L_Name &#060;&#062; W.L_Name AND T.F_Name &#060;&#062; W.F_Name<BR><BR>Anyone have a clue how to handle this?<BR>Thanks,<BR>Jesse

  2. #2
    Join Date
    Dec 1969

    Default Your SELECT is bogus...

    Try doing *JUST* the SELECT and see how many records you get.<BR><BR>You&#039;ll get the same 325,159.<BR><BR>Why?<BR><BR>Think about it:<BR><BR>Table T:<BR> T1 John Doe<BR> T2 Jim Jones<BR> T3 Tim Smith<BR><BR>Table M:<BR> M1 John Doe<BR> M2 Jim Jones<BR> M3 Tim Smith<BR><BR>SELECT T.ID, M.ID WHERE T.FirstName &#060;&#062; M.FirstName AND T.LastName &#060;&#062; M.LastName<BR><BR>Results:<BR> T1, M2<BR> T1, M3<BR> T2, M1<BR> T2, M3<BR> T3, M1<BR> T3, M2<BR><BR>See it? A "not equals" join is almost always a mistake. Find another way to express this. <BR><BR><BR>

Posting Permissions

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