Append or overwrite a record in one query?

jrmcanada2
jrmcanada2 used Ask the Experts™
on
I'd like to write a record to a table (in MS SQL Server) and have it add the record to the table (if no record with that key already exists) or overwrite the existing record (if a record with that key already exists). I could just delete the (possibly) existing record before doing an append query, but I was wondering if there was a simpler/better way.

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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.

:)
Walter RitzelSenior Software Engineer

Commented:
Contract ERP Admin/Consultant
Commented:
One way:
IF EXISTS (SELECT 1 FROM MyTable WHERE keyField = @keyValue)
    UPDATE MyTable
        SET MyField1 = @newValue1,
            MyField2 = @newValue2,
            MyFieldx = @newValuex
        WHERE keyField = @keyValue
ELSE
    INSERT INTO MyTable (keyField, MyField1, MyFIeld2, MyFieldx)
    VALUES (@keyValue, @newValue1, @newValue2, @newValuex)

Open in new window

Another way:
IF EXISTS (SELECT 1 FROM MyTable WHERE keyField = @keyValue)
BEGIN
    DELETE FROM MyTable WHERE keyField = @keyValue
    INSERT INTO MyTable (keyField, MyField1, MyFIeld2, MyFieldx)
    VALUES (@keyValue, @newValue1, @newValue2, @newValuex)
END

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Eric GreeneDirector of Technology

Commented:
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>

Open 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.
Distinguished Expert 2017
Commented:
Actually, a query that uses a Right-Join to the update source table will add or update.  It will not delete so if you need all three operations, you should use the Merge.  If you only need two, then the right-join works for Jet/ACE as well as SQL Server, et al.

Author

Commented:
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
Steve BottomsSr Network Admin

Commented:
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.
Top Expert 2016
Commented:
assuming your source data is tmpOrders

UPDATE tmpOrders LEFT JOIN tblOrders ON tmpOrders.OrderNumber = tblOrders.OrderNumber SET tblOrders.OrderDate = [tmpOrders].[OrderDate], tblOrders.CustomerCode = [tmpOrders].[CustomerCode], tblOrders.OrderNumber = [tmpOrders].[OrderNumber];

or using a right join

UPDATE tblOrders RIGHT JOIN tmpOrders ON tblOrders.OrderNumber = tmpOrders.OrderNumber SET tblOrders.OrderNumber = [tmpOrders].[OrderNumber], tblOrders.OrderDate = [tmpOrders].[OrderDate], tblOrders.CustomerCode = [tmpOrders].[CustomerCode];

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial