473,837 Members | 1,648 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Compare data in Tables

Leo
I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema, run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively) ; I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

Leo
Jul 23 '05 #1
3 1739
Ray
If its changes you are looking for try running SQL Profiler against it.
Filter for where writes > 0. Another solution would to write a script to
doing the all tables comparison. Something like

Create a table with tablename, checksumbefore, checksumafter, rowsbefore,
rowsafter, numberofrowsdif f
Write a cursor of all user tables
For each table
Get count of rows with select count(*)
calc the CHECKSUM of each row and write to individual temp tables
select count(*) from checksumafter where checksum not in checksumbefore
insert/update the table

By the end of the script you should have indentified which tables change and
by how much.
"Leo" <le*****@optush ome.com.au> wrote in message
news:Xn******** *************** **********@211. 29.133.50...
I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema,
run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively) ; I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

Leo

Jul 23 '05 #2
There is a software tool that can do this for you called DB Ghost
(www.dbghost.com). Its very fast at comparing data and can be run from
the command line for a fully automated process. A single command will
do any number of tables that you desire.

It's also the cornerstone of a full change management solution for SQL
Server databases i.e. it can build, compare and synchronize the schema
AND data directly from drop/create scripts held in a source control
system.

I highly recommend you check it out.

Jul 23 '05 #3
"Malcolm" <ma***********@ innovartis.co.u k> wrote in
news:11******** **************@ l41g2000cwc.goo glegroups.com:
DB Ghost


DB Ghost did exactly what I needed.

Thanks for your advice

Leo
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2090
by: Stephen | last post by:
I have to write a .Net application which can compare SQL Databases including things like: - DB structure, PK's, FK's, indexes and types of indexes i.e. should be able to detect if the same index has cascade set on one db and not on another, or unique on one and not the other, Constraints, Triggers, Stored procs, Users, Roles. I downloaded the SQL Data Compare 3.0 application 14 day trial and it seemed really good however does a bit more...
5
10886
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the new database. For instance, her old database has a table with Band Info in it. Her new database also has a table with Band Info in it but slightly different. I was wondering if there was an easy way to compare the fields from similar tables in...
8
7141
by: Vincent | last post by:
has any one seen a program to compare mdbs'. I have ran into a few of them, but none seem to really do that job. Basically what I need to do is, take 2 access mdb's and check the differences between the 2. i am talking about tables, forms, queries, the whole ball of wax. Most of the programs jus do tables, that is the easy part. Also I was wondering how you would compate text fiels progammatically and kick back a report using...
9
5610
by: geronimo_me | last post by:
Hi, I am atempting to compare part of a field with the whole of another field in access. Is this possible? Basically I have 2 tables with the following info: Table1 Field1 = MR.
9
6622
by: VMI | last post by:
I have two tables and I want to compare these two tables with a query( ie. "select * from A where B.key = A.key") and the result will be stored in a 3rd table (table C). is this possible? If necessary, I can create the schema for the 3rd table (C) since it'll be exactly like table A. But I'm more interested in being able to store the resulting set into another table. Thanks.
1
1887
by: Mark | last post by:
by m.r.davies I have 2 tables on seperate Db's (and servers) I want to use a datareader on the first table to pick the booking ref, and then use that booking ref to query the 2nd DB when i have the correct row in the 2nd DB I want to compare each field in the 2 tables to see if they match Every time i try and think how i end up with a while loop for the reader on table1 and a while loop for the reader on table 2 any ideas how i'm going...
0
1819
by: Shaw | last post by:
Our database is constantly updated (input data) from another DB, and sometimes it crashes our ASP.NET applications. My boss told me to write a DB utility app to check DB and make sure all apps are still running. The first step of few checks is to compare current DB tables with yesterday’s tables. I store yesterday’s tables at a XML file (DB is maintained by another guy, and certainly they don’t want me to make back copy for few...
5
6347
by: rcolby | last post by:
Evening, Wondering if someone can point me in the right direction, on how I would compare a system.guid with a system.byte. system.guid (pulled from sql server table with a data type of uniqueidentifier, originally taken from objectGUID from active directory domain)
6
7727
by: napatel04 | last post by:
Hi everyone, I would like to know if there is a quick query someone can help me write for the following scenario. I think I can do this with VBA but since this is suppose to be a temp. solution, I really do not want to spend too much time on it. I have two tables, 1 an old one which has work done in it with all the necessary columns correctly populated with the data and the second one I need which has the same columns but need to be...
5
3047
by: chazzy69 | last post by:
Ok heres what im trying to achive i am constantly updating the information in a table but know i what know if the data im replacing it with is new, i.e. whether or not i already had the data in the table. So my method for this is to first copy the information to be updated into another table (the 2nd table), then allow the information in the first table to updated (via deleting the current information and re-entering it). Then i what to...
0
9852
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
9696
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10642
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9420
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7824
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7014
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5680
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4062
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.