Home > Article > Using windows-batch to compare two tables

Using windows-batch to compare two tables

Micorsoft has already provided EXCEPT keyword to compare the result of two queries.

So we can easily find the unmatched records .

This batch file just implemented to compare records one or multiple tables in two servers by EXCEPT query automatically.

■ Directory Structure

│ CompareMul.bat
│ CompareOne.bat
│ ExSetEnv.bat
│ ExSqlCmdFile.bat
│ Tables.txt list of table names

├─result Result Folder
└─sql Sql Folder
receipt_tbl.sql Sql File

  • ExSetEnv.bat

Set the global variables.

rem @echo off
set DB_SERVER=SERVER
set DB_NAME_L=DataBase1
set DB_NAME_R=DataBase2
set DB_USER=user
set DB_PWD=pwd

set OUTPUT_FOLDER=result
set SQL_FOLDER=sql
  • ExSqlCmdFile.bat

Just excute the sql query with SqlCmd.

Sqlcmd -U%DB_USER% -P%DB_PWD% -S%DB_SERVER% -d%DB_NAME_L% -h-1 -s"	" -W -i"%1" -o"%2"
  • CompareOne.bat

Call ExSqlCmdFile.bat with input sql file path and out result file path as parameters

rem @echo off
cd /d %~dp0
set TABLE_NAME=%1
call ExSetEnv.bat
call ExSqlCmdFile.bat %SQL_FOLDER%\%TABLE_NAME%.sql %OUTPUT_FOLDER%\%1.txt
goto :EOF
  • CompareMul.bat

Loop through table names defined in Tables.txt and Call CompareOne.bat with table name as parameter Call CompareOne.bat with table name as parameter

rem @echo off
cd /d %~dp0
call ExSetEnv.bat
for /f %%p in (Tables.txt) do call :sub %%p
goto :EOF
:sub
call CompareOne.bat %1
goto :EOF
  • receipt_tbl.sql
SET NOCOUNT ON

SELECT  [columns for comparing]
FROM $(DB_NAME_L).DBO.$(TABLE_NAME)
EXCEPT
SELECT  [columns for comparing]
FROM $(DB_NAME_R).DBO.$(TABLE_NAME)
ORDER BY [columns for comparing]

SELECT  [columns for comparing]
FROM $(DB_NAME_R).DBO.$(TABLE_NAME)
EXCEPT
SELECT  [columns for comparing]
FROM $(DB_NAME_L).DBO.$(TABLE_NAME)
ORDER BY [columns for comparing]

How to Run

Then you just directly put

D:/>CompareMul.bat

or

D:/>CompareOne.bat tablename

in command line.

Other commands for memo

Output all user tables into files named as server name.

bcp "SELECT  [name] As name FROM [%DB_NAME%].[sys].[tables] where type = 'U' queryout %DB_SERVER%.txt -c -t\t -S%DB_SERVER% -U%DB_USER% -P%DB_PWD%

Directly excute a query by SqlCmd

Sqlcmd -U%DB_USER% -P%DB_PWD% -S%DB_SERVER% -d%DB_NAME_L% -Q%1
Advertisements
Categories: Article
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: