Compare Table Contents Procedure

  • Comments posted to this topic are about the item Compare Table Contents Procedure

  • You should also look at tablediff.exe to do that. 

    Yeah, being a command-line utility it's a little quirky to get used to, but I've found it works well enough once you understand how to use it.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Apart,

    Command(s) completed successfully.

    I don't understand how can I use such query.
    At what line should I set Database 1 and 2 and Table 1 and 2?

    Thank you

  • francesco.mantovani - Thursday, September 20, 2018 3:17 PM

    Apart,

    Command(s) completed successfully.

    I don't understand how can I use such query.
    At what line should I set Database 1 and 2 and Table 1 and 2?

    Thank you

    Hi,
    this is not a query but stored procedure, you pass your Databases and Tables when executing the procedure, might look something like this:

    EXECUTE @rc = [dbo].[Compare_Table_Content]
       @SourceDB = 'DB1'
      ,@TargetDB = 'DB2'
      ,@SourceSchema = 'dbo'
      ,@TargetSchema = 'dbo'
      ,@SourceTable = 'DummyTable1'
      ,@TargetTable = 'DummyTable2'

    GO

    This would compare DummyTable1 from DB1 to DummyTable2 in DB2. This does not work with linked servers.

    Scott, the intention of this is to avoid using TableDiff, do you want to tell someone who works with your code and needs to be able to compare two tables "go ahead, learn using TableDiff" instead of "pass the desired values here and there and off you go"? Keep in mind those people don't necessarily know anything about SQL except the few things they might hear in a chat with the Developer. Plus there is no easy way to integrate TableDiff into some ASP Page.

  • DinoRS - Tuesday, October 2, 2018 5:55 AM

    francesco.mantovani - Thursday, September 20, 2018 3:17 PM

    Scott, the intention of this is to avoid using TableDiff, do you want to tell someone who works with your code and needs to be able to compare two tables "go ahead, learn using TableDiff" instead of "pass the desired values here and there and off you go"? Keep in mind those people don't necessarily know anything about SQL except the few things they might hear in a chat with the Developer. Plus there is no easy way to integrate TableDiff into some ASP Page.

    I had no way to know that you were intentionally avoiding using tablediff.  I'm not still not sure of the reasoning behind that.  Why rewrite something that already exists as a system function?  The proc could use tablediff in the background if it wanted, one way or another.

    If you prefer to deliberately avoid it, that's fine, but then it'd be helpful if you'd just state that up front.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • DinoRS - Tuesday, October 2, 2018 5:55 AM

    francesco.mantovani - Thursday, September 20, 2018 3:17 PM

    Apart,

    Command(s) completed successfully.

    I don't understand how can I use such query.
    At what line should I set Database 1 and 2 and Table 1 and 2?

    Thank you

    Hi,
    this is not a query but stored procedure, you pass your Databases and Tables when executing the procedure, might look something like this:

    EXECUTE @rc = [dbo].[Compare_Table_Content]
       @SourceDB = 'DB1'
      ,@TargetDB = 'DB2'
      ,@SourceSchema = 'dbo'
      ,@TargetSchema = 'dbo'
      ,@SourceTable = 'DummyTable1'
      ,@TargetTable = 'DummyTable2'

    GO

    This would compare DummyTable1 from DB1 to DummyTable2 in DB2. This does not work with linked servers.

    Scott, the intention of this is to avoid using TableDiff, do you want to tell someone who works with your code and needs to be able to compare two tables "go ahead, learn using TableDiff" instead of "pass the desired values here and there and off you go"? Keep in mind those people don't necessarily know anything about SQL except the few things they might hear in a chat with the Developer. Plus there is no easy way to integrate TableDiff into some ASP Page.

    Hello, I followed your suggestion but it says:
    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@RC".

    the servers are not linked

  • ScottPletcher - Tuesday, October 2, 2018 8:28 AM

    DinoRS - Tuesday, October 2, 2018 5:55 AM

    francesco.mantovani - Thursday, September 20, 2018 3:17 PM

    Scott, the intention of this is to avoid using TableDiff, do you want to tell someone who works with your code and needs to be able to compare two tables "go ahead, learn using TableDiff" instead of "pass the desired values here and there and off you go"? Keep in mind those people don't necessarily know anything about SQL except the few things they might hear in a chat with the Developer. Plus there is no easy way to integrate TableDiff into some ASP Page.

    I had no way to know that you were intentionally avoiding using tablediff.  I'm not still not sure of the reasoning behind that.  Why rewrite something that already exists as a system function?  The proc could use tablediff in the background if it wanted, one way or another.

    If you prefer to deliberately avoid it, that's fine, but then it'd be helpful if you'd just state that up front.

    tablediff is an EXE file, this stored procedure allows you to compare two tables from two databases WITHIN SSMS. I find it usefull in so many scenarios: 
    - In case you don't have CMD access
    - In case you want to compare a table and its previous backup
    - in case you want to create a stored procedure that call this stored procedure and that alerts you in case of mismatch (you can create a "avoidable scenario" and receive an e-mail in case this happens)
    These are just a few scenarios.

  • francesco.mantovani - Tuesday, October 2, 2018 2:22 PM

    Hello, I followed your suggestion but it says:
    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@RC".

    the servers are not linked

    just leave @rc out of it, use something like


    EXECUTE [dbo].[Compare_Table_Content]
       @SourceDB = 'db1'
      ,@TargetDB = 'db2'
      ,@SourceSchema = 'dbo'
      ,@TargetSchema = 'dbo'
      ,@SourceTable = 'TBL1'
      ,@TargetTable = 'TBL2'
      ,@SourceColumn
      ,@TargetColumn
      ,@ExcludeColumn
      ,@SortOrder
      ,@debug

    the @rc just comes from right-clicking the procedure and selecting "Execute to" ... 🙂

    you have to define any @Source Variable you want to compare, so there must be a SourceDB, SourceSchema and SourceTable. If you don't pass values to the corresponding TargetDB etc. these values will be taken from @Source*. @SourceColumn and @TargetColumn only have to be set if you want to compare a specific column within 2 Tables only. you can pass a Sort Order for the Output with @SortOrder and last but not least @debug = 1 will show the SQL Statement that has been generated to get the output.

    @scott, consider that tablediff does row by row checking and you might have to do some stretches around NULL in comparing things, this might work much faster if you need to compare really large amounts of datasets. For me personally row by row is why I went with this approach. Granted tablediff offers different additions which this does not - like being able to generate a change script, that wasn't part of my intention. I need to be able to check discrepancies between two table contents and based on the output someone else will decide if we do data cleanups or not, automatically applying changes / creating change scripts was not the intention so in terms of needed functionality and performance I prefer to use the sproc.

  • Hello, still won't work,
    I run
    EXECUTE [dbo].[Compare_Table_Content]
     @SourceDB = 'FIRSTDB'
    ,@TargetDB = 'SECONDDB'
    ,@SourceSchema = 'dbo'
    ,@TargetSchema = 'dbo'
    ,@SourceTable = 'tblWorkstation'
    ,@TargetTable = 'tblWorkstation'
    --,@SourceColumn
    --,@TargetColumn
    --,@ExcludeColumn
    --,@SortOrder
    --,@debug

    and the reply says:

    (201 row(s) affected)

    (1 row(s) affected)
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'FROM'.
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'FROM'.
    Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword 'FROM'.
    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'FROM'.

    So there is still a problem

  • Try even with AdventureWorks2012 VS AdventureWorks2014

    EXECUTE [dbo].[Compare_Table_Content]
    @SourceDB = 'AdventureWorks2012'
    ,@TargetDB = 'AdventureWorks2014'
    ,@SourceSchema = 'dbo'
    ,@TargetSchema = 'dbo'
    ,@SourceTable = 'AWBuildVersion'
    ,@TargetTable = 'AWBuildVersion'
    --,@SourceColumn
    --,@TargetColumn
    --,@ExcludeColumn
    --,@SortOrder
    --,@debug

    the error says :

    (4 row(s) affected)

    (1 row(s) affected)
    Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword 'Database'.
    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'Database'.
    Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'Database'.
    Msg 156, Level 15, State 1, Line 11
    Incorrect syntax near the keyword 'Database'.

    I have installed your procedure on  "AdventureWorks2012", "AdventureWorks2014" and "master"

  • can you provide me with the output from @debug = 1 running your query? I'll have a look at it next week (I noticed myself some strange things with this procedure just yesterday).

  • Thank you, this is the output:

    (SELECT COLUMN_NAME INTO ##Resultset
    FROM [AdventureWorks2012].[INFORMATION_SCHEMA].[COLUMNS]
    WHERE TABLE_NAME = ('AWBuildVersion')
    AND COLUMN_NAME != ('*'))

    (4 row(s) affected)

    (1 row(s) affected)
    SystemInformationID, Database Version, VersionDate, ModifiedDate
    (SELECT CAST('source' as varchar(100)) as origin, SystemInformationID, Database Version, VersionDate, ModifiedDate FROM [AdventureWorks2012].[dbo].[AWBuildVersion]
    EXCEPT
    SELECT CAST('target' as varchar(100)) as origin, SystemInformationID, Database Version, VersionDate, ModifiedDate FROM [AdventureWorks2014].[dbo].[AWBuildVersion])
    union all
    (SELECT CAST('target' as varchar(100)) as origin, SystemInformationID, Database Version, VersionDate, ModifiedDate FROM [AdventureWorks2014].[dbo].[AWBuildVersion]
    EXCEPT
    SELECT CAST('source' as varchar(100)) as origin, SystemInformationID, Database Version, VersionDate, ModifiedDate FROM [AdventureWorks2012].[dbo].[AWBuildVersion])
    ORDER BY 1
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'Database'.
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'Database'.
    Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword 'Database'.
    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'Database'.

    try to install AdventureWorks2012 and AdventureWorks2014 on your machine. Is it working for you? 

  • code needs a quotename on the column list build
      (select ', ' + quotename(COLUMN_NAME) ---
         from ##Resultset

  • frederico_fonseca - Sunday, October 7, 2018 2:22 PM

    code needs a quotename on the column list build
      (select ', ' + quotename(COLUMN_NAME) ---
         from ##Resultset

    I don't understand, what am I supposed to do? Are you going to update your initial query? 
    Thank you

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply