SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query for DELETE and UPDATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djamit
Starting Member

16 Posts

Posted - 08/05/2013 :  10:59:49  Show Profile  Reply with Quote
I am trying to write a query to first delete the rows of a table and then update the table with new records which is imported from other database.
What is the best way to do this?

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 08/05/2013 :  11:24:43  Show Profile  Reply with Quote
If you meant that you want the two operations - deleting existing data and then inserting new records - to be atomic, then open a transaction within a try/catch block. There are examples on the link below. Experiment with that and make sure you understand what it is trying to do before you implement it for your actual problem and then roll it out to a production environment. http://technet.microsoft.com/en-us/library/ms175976.aspx

Example C on that page is usually what I end up using most often.

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/05/2013 :  11:29:51  Show Profile  Reply with Quote
Delete the rows:

USE [AdventureWorks2012]
GO

DELETE FROM [dbo].[NewOrders]
      WHERE <Search Conditions,,>
GO


Then insert the new records using either SELECT INTO or bulk insert commands:

EXAMPLE: 
SELECT orderid, orderdate, empid, custid INTO dbo.NewOrders FROM dbo.Orders;


OR
BULK INSERT dbo.NewOrders FROM 'c:\temp\neworders.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\ n' );

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/06/2013 :  00:48:41  Show Profile  Reply with Quote
or use MERGE which will do deletion , updation and also insertion (if you want) in single statement

something like


MERGE DestinationTable AS d
USING SourceTable AS s
ON s.RelatedColumn = d.RelatedCol
WHEN MATCHED THEN
  UPDATE SET DestTableCol = s.SourceCol,
             DestTableCol1 = s.SourceCol2,... othercolumns
WHEN NOT MATCHED BY TARGET THEN
  DELETE
WHEN NOT MATCHED BY SOURCE THEN
 INSERT (Col1,Col2,..)
 VALUES (s.Col1,s.Col2,..);




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/06/2013 :  04:17:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by visakh16

MERGE DestinationTable AS d
USING SourceTable AS s
ON s.RelatedColumn = d.RelatedCol
WHEN MATCHED THEN
  UPDATE SET DestTableCol = s.SourceCol,
             DestTableCol1 = s.SourceCol2,... othercolumns
WHEN NOT MATCHED BY TARGET SOURCE THEN
  DELETE
WHEN NOT MATCHED BY SOURCE TARGET THEN
 INSERT (Col1,Col2,..)
 VALUES (s.Col1,s.Col2,..);

There is a note to this approach. The MERGE is an "all or nothing" approach.
Using the code above will delete all rows in destination table (including all historical rows) that not currently present in the source table.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/06/2013 :  05:39:32  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

quote:
Originally posted by visakh16

MERGE DestinationTable AS d
USING SourceTable AS s
ON s.RelatedColumn = d.RelatedCol
WHEN MATCHED THEN
  UPDATE SET DestTableCol = s.SourceCol,
             DestTableCol1 = s.SourceCol2,... othercolumns
WHEN NOT MATCHED BY TARGET SOURCE THEN
  DELETE
WHEN NOT MATCHED BY SOURCE TARGET THEN
 INSERT (Col1,Col2,..)
 VALUES (s.Col1,s.Col2,..);

There is a note to this approach. The MERGE is an "all or nothing" approach.
Using the code above will delete all rows in destination table (including all historical rows) that not currently present in the source table.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA


We can always specify any additional condition you want to make sure DELETE only happens when that condition is satisfied by adding conditions by means of AND with NOT MATCHED BY SOURCE and TARGET clauses
What I gave was just a stub so in actual case OP might need to extend to suit the specific needs.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 08/06/2013 05:41:09
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/06/2013 :  05:55:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which condition is that Visakh?
See this repro. What happens to "Two" and "Three"?

OP can be in the situation that the source table DOES have all the latest information. If so, the NOT MATCHED BY SOURCE AND DELETE could be ok.
But if the source table is only the delta (new and changed rows since last time), the DELETE is dangerous.
DECLARE	@Target TABLE
	(
		Data VARCHAR(100) PRIMARY KEY CLUSTERED,
		Information VARCHAR(100) NOT NULL
	);

INSERT	@Target
	(
		Data,
		Information
	)
VALUES	('One', 'Info 1'),
	('Two', 'Info 2'),
	('Three', 'Info 3');

-- Before
SELECT	Data,
	Information
FROM	@Target;

MERGE	@Target AS tgt
USING	(
		VALUES	('One', 'Second revision'),
			('Four', 'First draft')
	) AS src(Data, Information) ON src.Data = tgt.Data
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.Information = src.Information
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					Data,
					Information
				)
			VALUES	(
					src.Data,
					src.Information
				)
WHEN	NOT MATCHED BY SOURCE
		THEN	DELETE;

-- After
SELECT	Data,
	Information
FROM	@Target;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/06/2013 :  06:03:44  Show Profile  Reply with Quote
Ok ..I got that part
What I thought was OP wanted to do delete under some specific conditions seeinng this


DELETE FROM [dbo].[NewOrders]
      WHERE <Search Conditions,,>


If thats case the same conditions can be checked in
NOT MATCHED BY SOURCE caluse by means of and condition

like

...
WHEN	NOT MATCHED BY SOURCE AND <Search Conditions,,>
		THEN	DELETE
...




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

djamit
Starting Member

16 Posts

Posted - 08/06/2013 :  07:22:11  Show Profile  Reply with Quote
The situation is like this: There is a table (MQ) where records are added daily. In a other table(CON) I want to insert the updated records in a scheduled job. I tought to delete all rows for CON first and then insert the the old records with the updated records from MQ
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/06/2013 :  08:14:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What you then want, is to make a condition for the update.

WHEN MATCHED AND target.SomeDateTimeColumn < source.SomeDateTimeColumn

in order make sure you have the "latest version" of the row.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

djamit
Starting Member

16 Posts

Posted - 08/06/2013 :  11:40:27  Show Profile  Reply with Quote
I have solve this by:
TRUNCATE TABLE CON
GO
INSERT into dbo.CON
Select MQ.PAT_NAME,MQ.IDA,MQ.MD_INITIALS
from MQ
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 08/06/2013 :  11:55:51  Show Profile  Reply with Quote
quote:
Originally posted by djamit

I have solve this by:
TRUNCATE TABLE CON
GO
INSERT into dbo.CON
Select MQ.PAT_NAME,MQ.IDA,MQ.MD_INITIALS
from MQ


This would work in most cases and is the simplest perhaps. The problem that everyone who replied have been trying to solve is to preserve the atomicity. Consider this:

Your truncate table statement succeeds.
But then the insert statement fails. It can fail for a variety of reasons, usually due to bad data such as null values in non-nullable columns, or foreign key constraint violations, or simply plain bad data.

If that happens, if you follow the approach you are taking, you end up with no data in the table. If that is an acceptable or expected behavior, then what you are doing is the right thing to do. If that is not the case, consider the other approaches posted.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/06/2013 :  12:08:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or wrap a transaction around the two statements.
BEGIN TRY
	BEGIN TRAN
	
	TRUNCATE TABLE dbo.Con

	INSERT	dbo.CON
		(
			Name,
			Ida,
			Initials
		)
	SELECT	PAT_NAME,
		IDA,
		MD_INITIALS
	FROM	dbo.MQ
	
	COMMIT TRAN
END TRY
BEGIN CATCH
	ROLLBACK TRAN
END CATCH



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

djamit
Starting Member

16 Posts

Posted - 08/06/2013 :  12:49:51  Show Profile  Reply with Quote
THANK YOU VERY MUCH SWEPESO
THIS WORKS FOR ME
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000