August 28, 2018 at 2:25 am
Comments posted to this topic are about the item Compare Table Contents Procedure
September 20, 2018 at 9:45 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
September 20, 2018 at 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
October 2, 2018 at 5:55 am
francesco.mantovani - Thursday, September 20, 2018 3:17 PMApart,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.
October 2, 2018 at 8:28 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) A socialist is someone who will give you the shirt off *someone else's* back.
October 2, 2018 at 2:22 pm
DinoRS - Tuesday, October 2, 2018 5:55 AMfrancesco.mantovani - Thursday, September 20, 2018 3:17 PMApart,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
October 2, 2018 at 2:27 pm
DinoRS - Tuesday, October 2, 2018 5:55 AMfrancesco.mantovani - Thursday, September 20, 2018 3:17 PMScott, 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.
October 3, 2018 at 5:10 am
francesco.mantovani - Tuesday, October 2, 2018 2:22 PMHello, 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.
October 3, 2018 at 2:01 pm
Hello, still won't work,
I runEXECUTE [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
October 3, 2018 at 2:07 pm
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"
October 5, 2018 at 2:31 am
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).
October 7, 2018 at 1:46 pm
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?
October 7, 2018 at 2:22 pm
code needs a quotename on the column list build
(select ', ' + quotename(COLUMN_NAME) ---
from ##Resultset
October 7, 2018 at 4:53 pm
frederico_fonseca - Sunday, October 7, 2018 2:22 PMcode 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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply