SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Table Comparison Procedure

By Sheraz Mirza,

This Procedure is comparing two table values, Specially usefull for QA having Back End Testing job, We can import any txt or excel file into our local database and then we can compare it with any other Database's Table. 

Procedure is taking 6 Parameters in which 1 is optional , 

@first_db = <first database name / Source DB>, 
@first_table = <fist DB's Table name>
@first_col_list = <column list of first table which need to be display>
@second_db = <second database name /Second DB>
@second_table = <second DB's Table name>
@second_col_list = <column list of second table could be ' ' in case of same values>
 

This code is displaying all common rows in both table and most important thing is , Procedure is making its own matching criteria by capturing all Primary Keys of Second Database which is source Database or you can say Production Database. and only Matching Rows will be displayed.

Hold on guys,the most important part of the Procedure is still remaining , you can get the query generated behind the scene and can modify that query as per your requirement. just clink of the tab Messages and you will get the Dynamic Query behind the scene, 

Just copy the query from message window and play, For Example you can get the rows which are not matching but just changing WHERE EXISTS clause with  WHERE NOT EXISTS

Alright Guys See you next time with something different

Total article views: 913 | Views in the last 30 days: 5
 
Related Articles
FORUM

First SQL Database

First Sql Database

FORUM

need help about procedure

procedure

FORUM

Stored procedure slower then query

Stored procedure slower then query

BLOG

5 Second Rule Doesn’t Apply to Dropped Databases or Does It?

5 Second Rule Doesn’t Apply to Dropped Databases or Does It? 5 Second Rule Doesn't Apply to Dropp...

FORUM

stored procedures in a database

stored procedures in a database

Tags
 
Contribute