Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kgundava
Yak Posting Veteran
66 Posts |
 Posted - 2011-08-03 : 11:46:56
|
| Hi All, I have a Table which resides in a Oracle and we copied the same table to a different database(ODM).I have created linked servers for both of these db's in sql server.Now e have to validate to verify whether every thing in table is copied rightly or not. As the data is huge I cannot do a minus query(because of time issues and network connectivity). So we are planning on doing a random sampling.First I picked 100 rows randomly from oracle and stored in a table in sql server. Now I would have to verify whether the same data exists in the ODM(2nd database) or not. To do so I am looking at different options right now i.e. 1)select * from LInkedserver..RANDOMDATA1 T,Randomdata1 S where ISNULL(S.[J_1BTAXLW3],0)=ISNULL(S.[J_1BTAXLW3],0) and ISNULL(S.[J_1AIDATEP],0)=ISNULL(S.[J_1AIDATEP],0) and ISNULL(S.[KZFME],0)=ISNULL(S.[KZFME],0) and ISNULL(S.[MWSKZ],0)=ISNULL(S.[MWSKZ],0) The where clause lists all the columns in the table(this is because lack of primary key in the tables) and the isnull is applied on all columns because some columns might be null.2) Using a join .If possible please provide me the sytax.Please let me know if this logic is right or not and if not any other options plz... |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-03 : 12:21:08
|
| Don't know if binary_checksum works the same way in Oracle, and whether the binary checksums would be the same if you generated it on a SQL Server and on an Oracle for rows that have identical data, but if those were true, you could compare the binary checksums. http://msdn.microsoft.com/en-us/library/ms173784.aspxOn SQL server, two identical rows would produce the same binary checksum, but there is a small probability that rows that are different could produce the same checksum. |
 |
|
|
kgundava
Yak Posting Veteran
66 Posts |
Posted - 2011-08-03 : 12:44:27
|
| Thanks for the reply...is there any way to join and avoid the Cartesian product in the above scenarioi.e Table AOracle (100) rows and Table BODM (1000000 Rows). Now i have to find whether the Table A 100 rows exists in the Table B (1000000 Rows) or not.There are no unique keys in those tables :(.Please let me know if there i a way in sql server...Thanks..... |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-03 : 13:41:24
|
If the two tables were both in SQL Server, you could join using the binary_checksum function like this:SELECT * FROM (SELECT BINARY_CHECKSUM(*) bc FROM TableA) AINNER JOIN (SELECT BINARY_CHECKSUM(*) bc FROM TableB) B ON a.bc = b.bc If you need to run this across servers, you may want to run it on the server that has the larger number of rows. Also you would need to add additional columns in the subqueries if you need to identify which rows match up. |
|
|
|
kgundava
Yak Posting Veteran
66 Posts |
Posted - 2011-08-03 : 14:09:38
|
| Thanks sunita..I am right now testing out the Binary check sum.But my major concern is that whether this would be equal or not across the databases.Please look this query.In this case both the tables have same no of rows.select BINARY_CHECKSUM([J_1BTAXLW3]) FROM QASAP_ARCCOL..SAPR3.[VBRP] except SELECT BINARY_CHECKSUM([J_1BTAXLW3]) FROM QASAPORACLE..SAPR3.[VBRP]Also is there any way to retrieve those 100 rows ......( Same question but cant help it :(....is there any way to join and avoid the Cartesian product in the above scenarioi.e Table AOracle (100) rows and Table BODM (1000000 Rows). Now i have to find whether the Table A 100 rows exists in the Table B (1000000 Rows) or not.There are no unique keys in those tables :(.Please let me know if there i a way in sql server... |
|
|
|
|
|
|
|
|
Data comparision!!!Urget....