January 15, 2015 at 8:57 am
Hi everyone,
I need a fonction to compare two tables to see what values is different. Like Server1.table1 and Server2.table1.
Thanks.
January 15, 2015 at 1:42 pm
Inner join the two tables together on their keys, and additionally filter on records where the fields you choose don't match.
;with a as
(
select myKey = 1, someVal = 'A' union all
select myKey = 2, someVal = 'B'
), b as
(
select myKey = 1, someval = 'A' union all
select myKey = 2, someVal = 'X'
)
select *
from a
inner join b
on a.myKey = b.myKey
and a.someVal != b.someVal
Note that if the columns permit nulls, you'll need to handle that as well
January 15, 2015 at 6:01 pm
Using JeeTee's sample data:
WITH a as
(
select myKey = 1, someVal = 'A' union all
select myKey = 2, someVal = 'B'
), b as
(
select myKey = 1, someval = 'A' union all
select myKey = 2, someVal = 'X'
)
SELECT *
FROM a
EXCEPT
SELECT *
FROM b;
Or vice versa if you want what's in b but not in a.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2015 at 9:19 am
Hi,
If i don't know what field exactly is different, and i need to find the field and the difference.
Thank you.
January 16, 2015 at 9:24 am
You'll have to write out the possible columns individually. If you have lots of columns, you could script out the statement against something like sys.columns for the table. If you want to have a boolean value tell you whether they are different you could use a case statement... something like
MyValue_IsDifferent = case when a.MyValue != b.MyValue then 1 else 0 end
January 16, 2015 at 9:44 am
Something I had lieing around, you might need to tweak it some if you're doing cross server/cross schema compares or if you have a composit key but you should get a list of every column that has different data, and the values in each column and it should handle nulls.
And that's assuming both tables have the same structure.
DECLARE @table_one varchar(50)
DECLARE @table_two varchar(50)
DECLARE @pkey varchar(50)
SET @table_one = '<table one>'
SET @table_two = '<table two>'
SET @pkey = '<primary key>'
CREATE TABLE temp_data
(
COLUMN_NAME varchar(50),
PKEY varchar(50),
TEST_DATA varchar(50),
PROD_DATA varchar(50)
)
DECLARE @col_name varchar(50)
DECLARE @exec_sql varchar(1000)
DECLARE @col_type int
DECLARE temp_con
CURSOR FOR
SELECT col.name, col.system_type_id FROM sys.columns col, sys.tables tab
WHERE col.object_id = tab.object_id AND tab.name = @table_two
AND (col.name != @pkey)
OPEN temp_con
FETCH NEXT FROM temp_con INTO @col_name, @col_type
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec_sql = 'SELECT ' + char(39) + @col_name + char(39) + ' AS COL_NAME, T.' + @pkey + ', T.' + @col_name + ' AS TEST_DATA, P.' + @col_name + ' AS PROD_DATA FROM ' + @table_one + ' T, ' + @table_two + ' P WHERE T.' + @pkey + ' = P.' + @pkey + ' AND ISNULL(T.' + @col_name + ',' + CASE WHEN @col_type = 167 OR @col_type = 61 OR @col_type = 175 THEN char(39) + char(39) ELSE '0' END + ') != ISNULL(P.' + @col_name + ',' + CASE WHEN @col_type = 167 OR @col_type = 61 OR @col_type = 175 THEN char(39) + char(39) ELSE '0' END + ')'
--SET @exec_sql = REPLACE(@exec_sql, 'zzzzz', @col_name)
INSERT INTO temp_data
EXEC (@exec_sql)
FETCH NEXT FROM temp_con INTO @col_name, @col_type
END
CLOSE temp_con
DEALLOCATE temp_con
SELECT DISTINCT COLUMN_NAME FROM temp_data
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply