[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1177
  • Last Modified:

Overwriting Duplicate SQL Data

I have an SQL statement that I am trying to run.  How the whole thing works is I import a table into my existing database.  The linked table is ARB1 and the one in the database is ARB.  The following is the SQL statement I have in my code to import data from ARB1 into ARB.

I get the error duplicate data.  How do I delete the duplicate data or delete the data first and then import the data?

DoCmd.RunSQL "INSERT INTO tblARB ( [Personnel ID Code] )SELECT tblStudent1.[Personnel ID Code], tblARB1.* FROM tblStudent1 LEFT JOIN tblARB1 ON tblStudent1.[Personnel ID Code] = tblARB1.[Personnel ID Code] WHERE (((tblStudent1.Instructor) Like [Who is the Instructor]));"

Thanks

JS
0
John Sheehy
Asked:
John Sheehy
2 Solutions
 
jrb1Commented:
This should delete any possible dups.

DoCmd.RunSQL "DELETE FROM tblARB where [Personnel ID Code] in (SELECT tblStudent1.[Personnel ID Code] FROM tblStudent1 LEFT JOIN tblARB1 ON tblStudent1.[Personnel ID Code] = tblARB1.[Personnel ID Code] WHERE (((tblStudent1.Instructor) Like [Who is the Instructor])));"
0
 
Raynard7Commented:
jrb is correct - run the above sql - then run your pre-existing sql statement for the inserts; this will remove the duplicates; then your insert will put everything in.
0
 
John SheehySecurity AnalystAuthor Commented:
I get the following error:

Duplicate Output destination 'Personnel ID Code'.

I have the link of personnel ID Code in both tables.  Is there a way around this?

JS
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
John SheehySecurity AnalystAuthor Commented:
Up'd the points
0
 
jerryb30Commented:
Real quick way:  Open query in design view.  You will see that you have [personnel id code] twice (in the arb.* and the
tblStudent1.[Personnel ID Code] field both having [Personnel ID Code] columns. Simply make sure the tblStudent1.[Personnel ID Code] column has no value in the [append to] field.
0
 
John SheehySecurity AnalystAuthor Commented:
Now that worked out great.  I am upping the points to 500 and split accordingly.

JS
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now