Append or overwrite a record in one query?

Nope.  You would need to do it in two steps: an UPDATE to update existing records, and an INSERT to add records where they do not already exist.

:)

You can use the merge command:
https://msdn.microsoft.com/en-us/library/bb510625.aspx

You want to use the Merge method to do that.

1. Create a User-defined Table Type

Open SQL Management Studio --> Databases --> <your db> --> Programmability --> Types --> User-Defined Table Types

2. Create fields

Enter all the fields, types, and sizes that are in the target table (I don't normally include auto-increment primary keys).

3. Build SQL Query with Table-Valued Parameter

What you do here depends on how the data is getting to SQL.  If you are writing a program and using ADO.NET, you would pass a populated datatable that is appropriately structured (The SqlDBType would be SqlDBType.Structured).
If you are writing a SQL query that collects data from various locations, extrapolates it, then dumps it in this table, then you would DECLARE a table variable that was structured the same as the User-Defined Table Type you created in steps 1 and 2.A query might look like this (assuming a target table name of "Sales_Statistics"

DECLARE @stats Table(salesman_id, weekly_avg, monthly_avg, total)
     INSERT INTO @stats SELECT <your query to gather the data here>

	MERGE Sales_Statistics AS TARGET
	USING @stats AS SOURCE
	ON (TARGET.salesman_id = SOURCE.salesman_id)
	
	WHEN MATCHED AND SOURCE.weekly_avg > 0 THEN
		UPDATE 
		SET weekly_avg=SOURCE.weekly_avg
			monthly_avg=SOURCE.monthly_avg,
			total=total
	
	WHEN NOT MATCHED BY TARGET AND SOURCE.MinistriesID IS NOT NULL AND SOURCE.MinistriesID>0 THEN
		INSERT (salesman_id, weekly_avg, monthly_avg, total)
		VALUES (SOURCE.salesman_id, SOURCE.weekly_avg, SOURCE.monthly_avg, SOURCE.total)

	WHEN NOT MATCHED BY SOURCE THEN
		DELETE
		
	OUTPUT <here you can include fields from INSERTED.<FIELD> OR DELETED.<FIELD> in order to evaluate results>

Select allOpen in new window

A small word of warning.  User-defined Table Types cannot be modified.  They can only be deleted and re-created.  Furthermore, they cannot be deleted if they are being referenced in an existing stored procedure.  You would have to copy the stored procedure text, drop the SP, drop the Type, re-create the type, then recreate the SP.  So, plan carefully -- it can be a pain-in-the-butt.

Hi Pat,

Can you give me an example of how this works? Say I have tblOrders with fields OrderNumber, OrderDate, and CustomerCode and the primary key is OrderNumber. What would the SQL statement look like?

James

There are a couple solutions above that might do what you need, but if you're trying to avoid adding conditional code in the calling application I would just use a stored procedure, personally: your code can make one TSQL SP call, pass the parameters needed, and the stored procedure will do the work without the front end needing to deal with conditionals.