Append or overwrite a record in one query?

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!
jrmcanada2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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.

:)
0
Walter RitzelSenior Software EngineerCommented:
0
dsackerContract ERP Admin/ConsultantCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

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

Eric GreeneDirector of TechnologyCommented:
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.
0
PatHartmanCommented:
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.
0
jrmcanada2Author 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
0
Steve BottomsSr Network AdminCommented:
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.
0
Rey Obrero (Capricorn1)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];
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.