This procedure will compare the two tables Table1 & Table2 and will display the value which is present in Table1 but not present in Table2.
/*
Object: Stored Procedure
Name: usp_SyncTables
Prepared by: Hemal Shah (Database Administrator)
Purpose :
This procedure will compare the two tables Table1 & Table2 and will display the
value which is present in Table1 but not present in Table2.
Input Para:-
Table1 - Table 1
Table2 - Table 2
pKeys - No of primary keys in Table1
pKey1 - Name of pk1
pKey2 - Name of pk2
pKey3 - Name of pk3
pKey4 - Name of pk4
T1ColumnList - List of fields in Table1
T2ColumnList - List of fields in Table2 ('' by default)
Condition: The structure of the both the tables should be same
Usage: exec usp_SyncTables 'Test1','Test2',1,'id','','','','id,fullname,ts'
Modification:-
------------------------------------------------------------------------------------
DateDescription
------------------------------------------------------------------------------------
*/
CREATE PROCEDURE usp_SyncTables
(
@Table1 varchar(100),
@Table2 Varchar(100),
@pKeys int,
@pKey1 varchar(100),
@pKey2 varchar(100)= '',
@pKey3 varchar(100)= '',
@pKey4 varchar(100)= '',
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = ''
)
AS
declare @SQL varchar(8000);
IF @T2ColumnList = '' SET @T2ColumnList = @T1ColumnList
IF @pKeys = 1
SET @SQL = 'SELECT a.* FROM ' + @Table1 + ' a' +
' LEFT OUTER JOIN ' + @Table2 + ' b' +
' ON ' + 'a' + '.' + @pKey1 + ' = ' + 'b' + '.' + @pKey1
+ ' WHERE ' + 'b' + '.' + @pKey1 + ' IS NULL'
IF @pKeys = 2
SET @SQL = 'SELECT a.* FROM ' + @Table1 + ' a' +
' LEFT OUTER JOIN ' + @Table2 + ' b' +
' ON ' + 'a' + '.' + @pKey1 + ' = ' + 'b' + '.' + @pKey1 + ' AND ' +
'a' + '.' + @pKey2 + ' = ' + 'b' + '.' + @pKey2
+ ' WHERE ' + 'b' + '.' + @pKey1 + ' IS NULL' + ' OR ' +
'b' + '.' + @pKey2 + ' IS NULL'
IF @pKeys = 3
SET @SQL = 'SELECT a.* FROM ' + @Table1 + ' a' +
' LEFT OUTER JOIN ' + @Table2 + ' b' +
' ON ' + 'a' + '.' + @pKey1 + ' = ' + 'b' + '.' + @pKey1 + ' AND ' +
'a' + '.' + @pKey2 + ' = ' + 'b' + '.' + @pKey2 + ' AND ' +
'a' + '.' + @pKey3 + ' = ' + 'b' + '.' + @pKey3
+ ' WHERE ' + 'b' + '.' + @pKey1 + ' IS NULL' + ' OR ' +
'b' + '.' + @pKey2 + ' IS NULL' + ' OR ' +
'b' + '.' + @pKey3 + ' IS NULL'
IF @pKeys = 4
SET @SQL = 'SELECT a.* FROM ' + @Table1 + ' a' +
' LEFT OUTER JOIN ' + @Table2 + ' b' +
' ON ' + 'a' + '.' + @pKey1 + ' = ' + 'b' + '.' + @pKey1 + ' AND ' +
'a' + '.' + @pKey2 + ' = ' + 'b' + '.' + @pKey2 + ' AND ' +
'a' + '.' + @pKey3 + ' = ' + 'b' + '.' + @pKey3 + ' AND ' +
'a' + '.' + @pKey4 + ' = ' + 'b' + '.' + @pKey4
+ ' WHERE ' + 'b' + '.' + @pKey1 + ' IS NULL' + ' OR ' +
'b' + '.' + @pKey2 + ' IS NULL' + ' OR ' +
'b' + '.' + @pKey3 + ' IS NULL' + ' OR ' +
'b' + '.' + @pKey4 + ' IS NULL'
EXEC (@SQL) 
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,855 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads
Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.
2008-08-13
6,696 reads
This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.
2001-10-28
1,599 reads
Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]
2001-10-28
1,271 reads