March 24, 2009 at 3:31 pm
I have two tables, table1 and table2 with same data structure and columns. Table1 has old data and table2 has new data. I need to compare each column of table1 and table2 and display only the changed data columns, for example if col1 and col2 have the differences, then we need to display the final output as follows:
select table1.col1, table2.col1, table1.col2, table2.col2
.......
Please let me know. Thanks.
March 24, 2009 at 4:24 pm
DECLARE @T1 AS TABLE (Col1 INT, Col2 INT, Col3 VARCHAR(5))
DECLARE @T2 AS TABLE (Col1 INT, Col2 INT, Col3 VARCHAR(5))
INSERT INTO @T1
SELECT 1,2,'Test1' UNION ALL
SELECT 2,3,'Test2' UNION ALL
SELECT 3,4,'Test3' UNION ALL
SELECT 4,5,'Test4'
INSERT INTO @T2
SELECT 1,2,'Test1' UNION ALL
SELECT 2,2,'Test2' UNION ALL
SELECT 3,4,'Test4' UNION ALL
SELECT 4,5,'Test4'
;WITH SameRows AS (
SELECT Col1, Col2, Col3 FROM @T1
INTERSECT
SELECT Col1, Col2, Col3 FROM @T2 )
SELECT T1.*, T2.*
FROM @T1 AS T1
INNER JOIN @T2 AS T2 ON T1.Col1 = T2.Col1
WHERE T1.Col1 NOT IN (SELECT Col1 FROM SameRows)
ORDER BY T1.Col1, T2.Col1
Output:
Col1 Col2 Col3 Col1 Col2 Col3
2 3 Test2 2 2 Test2
3 4 Test3 3 4 Test4
Maybe?
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 5:04 pm
This gives all the columns from table1 and table2, I only need the ones with differences. Thanks.
March 24, 2009 at 5:43 pm
Mh (3/24/2009)
This gives all the columns from table1 and table2, I only need the ones with differences. Thanks.
How would you like to see the output, considering that all rows in the result set must have the same number of columns and they must have the same datatype?
Please show as sample result set.
March 24, 2009 at 6:55 pm
Mh (3/24/2009)
This gives all the columns from table1 and table2, I only need the ones with differences. Thanks.
I see if you need such detail I would recommend looking at RedGate Data Diff or ApexSQL Data Diff they can do analysis a lot quicker for you. Trying to do it for a table for each column will be too much work. Unless you are doing it as a one off thing...
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 7:08 pm
select col1 from table2 where col1 not in (select col1 from table1)
do this for every table and i think it might work
March 24, 2009 at 9:16 pm
Below is my requirement, both tables have same number of columns and same datatype. Here I am just giving
an example of the tables.
Table1:
empID LastName FirstName
1 Michael Tom
2 Wacko Mark
3 Brad Gates
Table2:
empID LastName FirstName
1 Michelle Tom
2 Yo-Yo Mark
3 Brad Ma
From the above tables when I pass empID as a parameter: I should get the following output, only the columns with different values need to be displayed:
output for for empId:1
empID LastName
1 Michael
1 Michelle
output for for empId:3
empID FirstName
3 Gates
3 Ma
Please let me know. Thanks.
March 24, 2009 at 10:00 pm
How would you like to see the output, considering that all rows in the result set must have the same number of columns and they must have the same datatype? Are you expecting to get a different result set for every column in the table that you want to compare.
Please show a sample result set.
Also, just out of curiosity, why is this so urgent?
March 25, 2009 at 7:40 am
In sqlserver -2005 i am created three columns
Column name datatype
one is BeginTime datetime
second EndTime datetime
third TotalTime in properties computed column specification Formula ([EndTime]-[BeginTime])
10/12/2009 12:00:00 AM
12/12/2009 12:00:00 AM
result come like -- 3/3/1900 12:00:00 AM but this is wrong .
Please any one help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply