• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

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!
0
jrmcanada2
Asked:
jrmcanada2
3 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Shane KahkolaDirector of I.T.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.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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