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)
 verify help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

176 Posts

Posted - 01/10/2013 :  06:55:51  Show Profile  Reply with Quote
I am building a stored procedure to archive old clients off our live database. When insert a record into the client_arc database, what is the easiest way of checking that the record count matches in both databases before deleting from the live tables. I have an extract to test as a straight forward query so i can nesure i am getting the right results


DECLARE @licence varchar(6) = '999999'
Declare @sql_Return int = 1

INSERT INTO [Client_Arc].[dbo].[tbBOSS_client]
SELECT * FROM Boss.dbo.tbBOSS_client WHERE licence = @Licence

IF (@@ERROR <> 0) or (@@ROWCOUNT = 0)
BEGIN
SET @sql_Return = 1
-- RETURN @sql_return
PRINT 'FAILED'
END
ELSE
BEGIN
/* CHECK RECORDS MATCH IN BOTH TABLES */

Best way to check if the records match ? and return 1 if no match and 0 if matched


END

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/10/2013 :  07:36:50  Show Profile  Reply with Quote
Wrap the delete and insert into a try catch block and a transaction, exactly like the example "B. Using TRY…CATCH in a transaction" on this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx. As long as the where clauses of the insert and delete are the same and insert is done before the delete, the deleted rows will be inserted into the archive table.

Alternatively, you can use the output clause to delete the rows and simultaneously insert the data into the archive table. Output clause has some restrictions - for example, you cannot have any check constraints or rules in the archive table columns. A good article with examples is on this page: http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/

Edited by - sunitabeck on 01/10/2013 07:37:54
Go to Top of Page

Pete_N
Posting Yak Master

176 Posts

Posted - 01/10/2013 :  08:03:25  Show Profile  Reply with Quote
The try catch looks like a good option. Just to clarify, if any record or part record in in this case not inserted , that would gerenate an Error, is that correct?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/10/2013 :  09:53:30  Show Profile  Reply with Quote
Short answer: Correct.

The insert is a single statement that has the ACID properties, so either all the rows you specify should be inserted, or none will be inserted. Similarly the delete statement - either it will succeed and delete all the rows, or none will be deleted.

The purpose of the try-catch block and explicit "BEGIN TRAN" is to apply a similar requirement on the two statements together - i.e., both the insert and delete should succeed together, or both should fail.
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.06 seconds. Powered By: Snitz Forums 2000