Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 verify help

Author  Topic 

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-01-10 : 06:55:51
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-01-10 : 07:36:50
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/
Go to Top of Page

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-01-10 : 08:03:25
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-01-10 : 09:53:30
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
   

- Advertisement -