Need to surpress meesages when IGNORE_DUP_KEY = ON

The way to do this is use an outer join.
 
New data ===> outer join to existing data.

then check for the key on the existing data, and if null, you want to insert the record.

 NOT EXISTS is another way to achieve this (see section E):

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-ver15

Which way you go at it depends on the circumstances.   If your are doing this on the SQL side, you'd want to use NOT EXISTS 

Jim.

Thanks... I've tried using if NOT EXISTS (thanks for the code Máté  )
Unfortunately, it does not work.. here's the problem..

The Source-table allows duplicates... (two customers may buy the same part.)
When I try to run the Insert Into query the query fails because it of the primary key violation
ugh?

So I guess I have to eliminate dups from the source file before appending to the Master List??
I'm not sure how.. or if there is a better solution??

I gave you 2 solutions yesterday but they are all ignored...

HainKurt.. I hope I haven't offended you.. I did try to make your code work but didn't understand it enough to use.. Keep in mind I am an T-SQL newbie... I've never written code to create a table or view, and didn't understand what an Alias is or how to use them....(I'm a little better today).... I tried using the IGNORE_DUP_KEY solution because it didn't require any new code..  If I could turn off the message its definitely the shortest path for me.. I looked at your code again this morning..and I still couldn't understand it enough to make it work... I think the Alias is confusing me..

I tried using the IGNORE_DUP_KEY solution

what is this solution exactly?
you re-created table with a key that ignores duplicates?

then how do you insert records? 

here is the solution I proposed before:

insert into PROD
select distinct * from PRODX x where not exists (
  select 1 from PROD p where p.ID=x.ID and p.Vendor=x.Vendor
);

Select allOpen in new window

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=85fc56fa90bc13e93238159138aa71d9
here, I used same insert twice...
first one inserted unique, non existing ones into PROD
and second one inserted none, since all exists in PROD

HainKurt... I always enjoy getting your code because your code writing is so much above mine that I learn a lot.. but I do need to understand it to modify it when necessary...

In my discussion of the problem to keep things simply I did not mention that there are actually three fields which I need to append to the MasterFile.. the partnumber, vendernumber, and ordernumber.

The partnumber is the same as ordernumber except it has all spaces, slashs, dashes, etc removed.. I use it for searching because otherwise searching for numbers that include these characters is really difficult.

The ordernumber is the number that the manufacturer uses... but the problem is even the manufacturers are not also consistent.. sometimes numbers have spaces and other times hyphens...if they use any of them at all...

So my primary key is the partnumber + vendornumber.. but I also need to add the ordernumber to my masterdatabase.. ..
In Access I would use a GroupBy and I would include First(SourceTableOrView.OrderNumber) AS FirstOfOrderNumber in the SELECT clause...

I tried adding this to my SQL code but it threw out error messages because o the term FIRST.

Your code leaves me with many questions... also PartNumber is alphanumeric

I just updated my comment

or you can use MERGE

 MERGE PROD AS TARGET
 USING (select distinct * from PRODX) AS SOURCE
    ON (TARGET.ID = SOURCE.ID and TARGET.Vendor=SOURCE.Vendor) 
  WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Vendor) VALUES (SOURCE.ID, SOURCE.Vendor);

Select allOpen in new window

ok, I am not sure what is your master table look like, and where it is
and what is your access table look like
if you post a screenshot of what access table has
and what sql tab le has
and what method are you trying to copy records from access to sql
we can help more...

You should be able to add code to handle, i.e. ignore, the warning message.  Check the app interface you're using for how to handle the msg.  (I'm a DBA now, not a developer any longer, so, sorry, I don't know how to write the code to handle it).

It's not an error message so unfortunately a BEGIN TRY ... END CATCH won't prevent the msg.

The "msg requires acknowledgement" must be an Access thing, because SQL Server procs, scripts, etc., go on executing after issuing the msg, with no intervention required.

Here is the exact code I have been using in Access:
The Structure of the MasterDatabase and the QuotesDatabase is exactly the same with respect to these fields.
Parts are added to the QuotesDatabase every day...and the end of each day we perform maintenance where we need to create one record for each part we have quoted in the MasterDatabase.

So if we quote the same part to two different customers it appears in the QuotesDatabase twice... but only once in the MasterDatabase.

if once customer asks for a quote for 2N 2222 and second customer asks for a quote for 2N-2222 on the same day, the records created in the QuotesDatabase would be:

VenderNumber   PartNumber   OrderNumber
12345                  2N2222         2N 2222
12345                  2N2222         2N-2222

in the code above.. if the VenderNumber+PartNumber is not in our MasterDatabase then we would add 12345 and 2N2222 and 2N 2222 (because it came first, we don't really care which, but we can only store one number)

IN Access.. we simply attempt to append all the records in QuotesDatabase to the MasterDatabase... litterly millions of records... but only the unique combinations are added.. which is what we want...  Its a shame that IGNORE-DUP-KEY pushes out that message... no other coding changes would be needed

My Access code is below..

docmd.setwarnings false
strSQLtext = "INSERT INTO MasterDatabase( SearchNumber, LineID, OrderNumber, PartOrigin, RecordCreatedDate, RecordCreatedBy ) "
                strSQLtext = strSQLtext & "SELECT QuotesDatabase.SearchNumber, QuotesDatabase.LineID, QuotesDatabase.OrderNumber, 'Rebuild-Orphan' AS Exp1, #" & Date & "# AS Exp2, 'ROB' AS EXP3 "
                strSQLtext = strSQLtext & "FROM QuotesDatabase LEFT JOIN MasterDatabaseON (QuotesDatabase.LineID = ComponentMaster.LineID) AND (QuotesDatabase.SearchNumber = ComponentMaster.SearchNumber) "
                strSQLtext = strSQLtext & "WHERE (((QuotesDatabase.SearchNumber)<>'x' And (QuotesDatabase.SearchNumber)<>'Reserved' And (QuotesDatabase.SearchNumber)<>'' And (QuotesDatabase.SearchNumber) Is Not Null) AND ((QuotesDatabase.Quantity)>0)); "

Scott.. are you suggesting that the message that alerts me that a record has been skipped may be produced by Access not SQL?  If so that may explain why I though it worked last night.. and then learned of the message when I ran the code from Access..I wonder if its actually considered a warning that I can turn off... might be worth additional testing...

so why dont you just run this query from Access?

strSQLtext = "insert into MasterDatabase (SearchNumber, LineID, OrderNumber, PartOrigin, RecordCreatedDate, RecordCreatedBy) select distinct q.SearchNumber, q.LineID, q.OrderNumber, 'Rebuild-Orphan' AS Exp1, #" & Date & "# AS Exp2, 'ROB' AS EXP3 from QuotesDatabase as q where not exists (select 1 from MasterDatabase m where m.SearchNumber=q.SearchNumber and m.LineID=q.LineID) and q.SearchNumber is not null and q.SearchNumber not in ('x','Reserved','') and q.Quantity>0"

Select allOpen in new window

ie, this query

insert into MasterDatabase (SearchNumber, LineID, OrderNumber, PartOrigin, RecordCreatedDate, RecordCreatedBy)
select distinct q.SearchNumber, q.LineID, q.OrderNumber, 'Rebuild-Orphan' AS Exp1, #" & Date & "# AS Exp2, 'ROB' AS EXP3
  from QuotesDatabase as q
 where not exists (select 1 from MasterDatabase m where m.SearchNumber=q.SearchNumber and m.LineID=q.LineID)
   and q.SearchNumber is not null
   and q.SearchNumber not in ('x','Reserved','')
   and q.Quantity>0 

Select allOpen in new window

HainKurt... the problem with running from Access is that I've been experiencing system resource exceeded and other errors due to the current size of the database... Microsoft.. as well as many ee experts, have convinced me to start migrating to the SQL and stored procedures.

So I moved the tables from an Access BE to SQL Server backend..

Is the second query the same as the query I provided?  Are you suggesting I use that in Access instead of the query I submitted?

it should be the same query...
the one I posted will insert  only new ones, you dont need anything else...

Likely best comments will be provided, when you attach a text copy (not image) of your table schema.

Right now comments are guesses, as no schema has been provided for review.

Thanks again, everyone. I am still working on this.
David.... I'm not able to post in a public forum our table schema as it includes proprietary information.

Just to clarify... The MasterDatabase includes one record for every electronic component that we know of, and more than 200 fields that we use to store information unique to that record. This table holds well over 1 million records.

Each time we receive a new part to sell, or even a request to quote a part we do not have in stock, we use code to add t record to our MasterDatabase.  If nothing ever went wrong, we would not need this routine.

However crap happens, power failures, machines crash or are accidentally rebooted, changes are made after parts are appended to the MasterDatabase, database crash, compact and repairs delete records, and so on, so we need a way to fix problems once they occur which cause records to not be included in our MasterDatabase.. To do this in the MS Accesss world it was simple.. A simple Insert Into was all that was needed with warnings turned off.  Records which had the same PK would be rejected as I want, and only those which are not already in the MasterDatabase would be appended.

TSQL seems to have the same capability.. but it throws a message which requires acknowledgment and therefore will not run unattended....

Pat,  

   I'm going to toss in a few comments and try and clarify some things.

1.  With a SQL server BE, you can run the SQL in Access, execute it as a pass-through query, or execute it as part of a stored procedure.

2. When running from Access, JET/ACE is involved and handling the execution.   That means VBA can be used and the SQL is written so that JET/ACE understands it (everything you've been used to).   It also means you can do operations with local tables.

3. A pass-through must have SQL written so the back-end understands it.   JET/ACE is no longer in the picture.  It simply passes the SQL off to the BE for execution.
 
4. A stored procedure is like a mini-program and you can do all sorts of things with it.

 Sorry if you know all that already, but since you were just starting out in this direction, I though it would be worth stating.

 With all that said, for basic operations the differences in SQL with what JET/ACE and SQL Server understands are for the most part minor.   The only place where you'll get caught right off is with 'LIKE' (wildcards are different).

 Since this is a basic operation, don't overthink it.  Setup a SQL statement using Access.  You want unique rows on the input side.  Start with that.  Then add the NOT Exists for the output side.  Use a WHERE on the input side temporarily to restrict the number of records while you are testing.

 After that, when you have it working as it should, use SSSM to execute the SQL to make sure SQL Server will understand it.

 Once you have it working in SSMS, then set it up as a pass-through in Access, or create a stored procedure and call that.

You should find it's nothing more that what Máté already posted:

INSERT INTO MasterData(ventdernumber, partnumber)
SELECT DISTINCT ventdernumber, partnumber
FROM SourceTableOrView src
WHERE NOT EXISTS(SELECT * FROM MasterData m WHERE m.ventdernumber = src.ventdernumber AND m.partnumber = src.partnumber)

Select allOpen in new window

  The SELECT DISTINCT should be eliminating the dups on the input side.

HTH,
Jim.

Scott.. are you suggesting that the message that alerts me that a record has been skipped may be produced by Access not SQL?  

No, sorry, that was not my intention.

The msg is indeed produced by SQL Server.  It's Access's reaction to that msg occurring that is the issue. SQL Server itself does not that see that msg as an error, only as a warning.  That is, if something is forcing the task to stop until that msg is dealt with, that can only be someone on the app side, since SQL Server continues processing after that msg is issued with no intervention required.

Thanks all!

I wound up using a staging table but all of your comments were very helpful.

This task was more complex than it should have been due to a "bug" that was found in Access.  I had records in the searchnumber filed of my MasterData table which actually used a Unicode dash (-) character.  Access was converting that character to ASCII 45 which is a hyphen.  As a result, the my code was doomed to fail because the comparison between the searchnumber fields in the two tables would never match...

Thanks again. This was a great learning experience.

Special thanks to HainKurt, Jim, and Scott for taking so much time out to teach me about SQL Server.

Jim.. my next project will be learning how to run a pass thru in code!

<<Jim.. my next project will be learning how to run a pass thru in code! >>

It's not overly difficult.  The simplest way is via a querydef:

          Dim qdfPassthrough As DAO.QueryDef

          ' Find parameter record in tblemailtemplates
30        Set qdfPassthrough = CurDb().CreateQueryDef("")
40        qdfPassthrough.Connect = CurDb().TableDefs("tblemailtemplates").Connect
50        qdfPassthrough.sql = "Select * From tblemailtemplates WHERE Reference = '" & strReference & "' AND Company = '" & strCompany & "' AND Location = '" & strLocation & "'"
60        Set rstemailtemplates = qdfPassthrough.OpenRecordset()

Select allOpen in new window

 The main thing to understand is that you are by-passing JET and sending something directly to SQL server for execution.  That means:

a. No Joins to local tables.
b. No VBA expressions
c. SQL Syntax needs to be for SQL Server, not JET's.

  I would suggest you pickup the Access Developers Handbook published by Sybex, or the 

Access and SQL Server Developer's Handbook also by Sybex.   Both are dated at this point, but you'd be able to pick them up cheap (like $5 used) and everything they discuss is still applicable like the technique above.

 Both also go into calling stored procedures both from DAO and ADO, which is something you'll want to explore as well.

Jim.