April 25, 2007 at 8:55 am
Hello Everybody,
I want to compare the whole row in two tables and if there are not any column changes ignore and if theere are any changes
i should pick them up.
Could any body help me with a solution or if you can give a kick start also i can pick it up from there to.
Thanks IN ADVANCE!
April 25, 2007 at 10:34 am
You may want to play with the EXCEPT AND INTERSECT
select
* from table_1
select
* from table_2
select
* from table_1
except
select
* from table_2
April 25, 2007 at 11:26 am
If these are on the same server
For SQL 2000 you don't have those options but this can help
SELECT
A.KeyColumn,
B.KeyColumn
FROM
(select KeyColumn, CHECKSUM(*) CheckMe from dbName1.dbo.Table1) A
FULL JOIN
(select KeyColumn, CHECKSUM(*) CheckMe from dbName2.dbo.Table2) B
ON
A.KeyColumn = B.KeyColumn
WHERE
A.KeyColumn IS NULL
OR B.KeyColumn IS NULL
OR A.CheckMe != B.CheckMe
If SQL 7 then you will have to join the two and do a list of the columns like so
SELECT
A.KeyColumn,
B.KeyColumn
FROM
dbName1.dbo.Table1 A
FULL JOIN
dbName2.dbo.Table2 B
ON
A.KeyColumn = B.KeyColumn
WHERE
A.KeyColumn IS NULL
OR B.KeyColumn IS NULL
OR A.Col1 = B.Col1
OR (A.Col1 IS NULL AND B.Col1 IS NOT NULL)
OR (A.Col1 IS NOT NULL AND B.Col1 IS NULL)
OR A.Col2 = B.Col2
OR (A.Col2 IS NULL AND B.Col2 IS NOT NULL)
OR (A.Col2 IS NOT NULL AND B.Col2 IS NULL)
...
Possibly better to buy a tool such as Red Gates DataCompare if you need to do this often and for multiple tables.
April 25, 2007 at 1:33 pm
Thanks For Your Help!
But I have 10 columns in a table can i use the same above T-sql.
please let me know!
April 25, 2007 at 1:45 pm
Which version of SQL and what are the tables definition but you can always use option for SQL 7.
April 26, 2007 at 7:02 am
This is on sql server 2000.
Here is how it is
i have two tables
t1 and t2 both have same columns
t1
uid fname lname dept unit MgrName MgrID
1 rob Tom HR Bus Nancy 23
1 rob Tom Web Public Kathy 26
2 bor Mott HR Bus Thomas 25
t2
1 rob Tom HR Bus Thom 24
1 rob Tom Web Public Kathy 26
2 bor Mott HR Bus Thomas 25
From the above scenario,i should pick up
UID 1(ONLY 1st row) as change because his mgrname changed
and ignore UID2 and second row because nothing changed.
Thanks in Advance!
April 26, 2007 at 12:25 pm
You are out of luck using the EXCEPT which works beautifully in SQL2005 for your case. However, T-SQL suggested above also works for SQL-2000.
April 26, 2007 at 12:38 pm
This just came up here today. We were having some trouble with replication. We wound up using:
http://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=596
Though we had to make some changes to it since our DB uses a case-sensitive collation and the case used in its references to system tables was inconsistent.
Anyhow, it worked.
December 20, 2007 at 4:08 am
Hello Everybody,
I want to compare the whole row in two tables and if there are not any column changes ignore and if there are any changes
i should pick them up.
Could any body help me with a solution or if you can give a kick start also i can pick it up from there to.
I started of with the following query which absolutely works fine BUT ONLY FOR LESS NUMBER OF COLUMNS IN TABLES,
IF THERE ARE MORE NUMBER OF COLUMNS THEN THE FOLLOWING APPROACH IS NOT FEASIBLE
SELECT MIN(TableName) as TableName, ID, COL1, ADDDATE
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.ADDDATE
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.ADDDATE
FROM B
) tmp
GROUP BY ID, COL1,ADDDATE
HAVING COUNT(*) = 1
ORDER BY ID
Thanks IN ADVANCE!
December 20, 2007 at 4:21 am
Do Antares's solutions not work? If no, tell us why not and we'll see if we can help.
John
December 20, 2007 at 4:27 am
Hello Everybody,
I want to compare the whole row in two tables and if there are not any column changes ignore and if there are any changes
i should pick them up.
Could any body help me with a solution or if you can give a kick start also i can pick it up from there to.
I started of with the following query which absolutely works fine BUT ONLY FOR LESS NUMBER OF COLUMNS IN TABLES,
IF THERE ARE MORE NUMBER OF COLUMNS THEN THE FOLLOWING APPROACH IS NOT FEASIBLE
SELECT MIN(TableName) as TableName, ID, COL1, ADDDATE
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.ADDDATE
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.ADDDATE
FROM B
) tmp
GROUP BY ID, COL1,ADDDATE
HAVING COUNT(*) = 1
ORDER BY ID
IF ANY ONE HAS ANY OTHER SOLUTION PLEASE HELP ME OUT
Thanks IN ADVANCE!
December 20, 2007 at 4:41 am
Use CHECKSUM_AGG with BINARY_CHECKSUM to detect changes in a table.
BINARY_CHECKSUM ignores columns of noncomparable data types in its computation. Noncomparable data types are text, ntext, image, and cursor, as well as sql_variant with any of the above types as its base type.
Refer to SQL server books online for usage syntax
Let me know if it helps
December 21, 2007 at 3:38 am
Although it was originally intended for use in replication the tablediff utility is specifically designed for the problem you're trying to solve. Easy to use, very quick and generates you the SQL to "correct" the data.
You can find it in C:\Program Files\Microsoft SQL Server\90\COM if you installed to the default location.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply