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.
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 resultsDECLARE @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/ |
|
|
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? |
|
|
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. |
|
|
|
|
|
|
|