Comparing Records

Is there a Stored Procedure that compares 2 records and give you the
diffrences between them? Or do I have to manually compare each record?You will have to write one depending on what you want to compare and how you
want to retrieve the differences. For direct data comparison there are
certain 3rd party tools from companies like RedGate which you can buy off
the shelf.
Anith|||I wanted to do this from a trigger. ie see what changes there are from
the old record to the new record.|||You'd have to use the inserted and deleted tables within the trigger to do
this. Check out the topic CREATE TRIGGER in SQL Server Books Online; there
are certain examples which details how you can use them.
If you still have difficulty in coming up with a required solution, pl.
refer to www.aspfaq.com/5006 and post required information for others to
repro your problem.
Anith|||Thanks, I know how to do triggers. But you answered my question so
thanks.

Compare two tables query result

dear all

i need to write SQL Query that compares 2 tables as folows:

lets say i have:

table A with A.A, A.B, A.C columns (Table A with Columns A,B,C)

table B with B.A, B.B, B.C columns (Table B with Columns A,B,C)

i need to compare and to mark (get in result Query) each row lets say in table B that was changed from table A

For Example

Table A

A B C

12 15 hello

15 17 adv

asd 19 23

14 rer 89

Table B

A B C

12 15 hello

15 19 adv

*** 19 23

14 rer 89

the result is the records which was changed from A to B

A B C

15 19 adv

*** 19 23

Thnks alot!

That is probably non-trivial in terms of performance:

Select

CASE WHEN A.A != A.A THEN '***' ELSE A.A AS A
CASE WHEN A.B != A.B THEN '***' ELSE A.B AS B

CASE WHEN A.C != A.C THEN '***' ELSE A.C AS C
FROM TableA
INNER Join TableB
ON A.IdentifierWhichcannotchange = b.IdentifierWhichcannotchange

You will need the IdentifierWhichcannotchange as elsewhere you cannot identify the matching rows, e.g. how to know that the if two rows look the same after the change from which rows they were actually sourced on.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

can you explain in more details the A.IdentifierWhichCannotchange, B.IdentifierWhichCannotchange?

the query result dows not change anything on the tables

p.s i run the query in msAccess

And very important thing that i forgotten to mention

The firsrt 3 columns are my Unique (Primary Key)

|||

You need an identitfier in both tables which cannot change, otherwise you won′t be able to match the rows. its like building a sum from multiple values and then afterwards trying to identity which values the sum is based on. In your case, if you have two rows containg the values

1;23;27
3;23;59

which are changed to

4 (changed);23;54 (changed)
4 (changed);23;55 (changed)

How would you know which row refers to which in the other table. they have lost their identifying attribute here (the combination of values). if you had some identifying value which is not supposed to change then you can easily compare the values in the rows refering to the non-changeable attribute (like I did in the query above)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

OK

first thanks for the replies

so here is my tables and data:

t04 A B C D E F H I J 019 003 000028 1 004604070 1 06/05/2007 01/01/2001 003 019 003 000036 1 004604070 1 06/05/2007 01/01/2001 003 019 003 000044 1 030557268 2 06/05/2007 01/01/2001 003 t05 A B C D E F H I J 019 001 000004 1 028354470 1 15/05/2007 28/02/2002 001 019 003 000028 1 004604070 1 06/05/2007 31/03/1986 003 019 003 000036 1 004604070 1 06/05/2007 31/12/1989 002 019 003 000044 1 030557268 2 06/05/2007 31/03/1986 003 result A B C D E F H I J 019 001 000004 1 028354470 1 15/05/2007 28/02/2002 001 019 003 000036 1 004604070 1 06/05/2007 31/12/1989 002

my Id is Columns A,B,C

in the result we can notice 2 things

1) 019 001 000004 was added because it exists in t05 and not t04

2) the value in col j for 019 003 000036 was changed from 003 to 002

the goal is to create table of diffrences!

in general i can unified columns A,B,C to one column (if it helps) 019001000004 for examle

i quess i clarified my self better

|||

That will be something like:

Select

CASE WHEN A.D != A.D THEN '***' ELSE A.D AS D,

CASE WHEN A.E != A.E THEN '***' ELSE A.E AS E,

CASE WHEN A.F != A.F THEN '***' ELSE A.F AS F,

CASE WHEN A.H != A.H THEN '***' ELSE A.H AS H,

CASE WHEN A.I != A.I THEN '***' ELSE A.I AS I,

CASE WHEN A.J != A.JTHEN '***' ELSE A.J AS J

FROM TableA A
INNER Join TableB B
ON

A.A = B.A AND

A.B = B.B AND
A.C = C.C

Jens K. Suessmeyer

http://www.sqlserver2005.de

Compare Triggers Values

Hi.
How to create a trigger before insert that if a new row is giong to be
inserted in a table the trigger compares a value of a column from the
table with the value of a column in the record to be inserted...
Regards
Muhammad Bilal
<bilal_4x@.hotmail.com>
*** Sent via Developersdex http://www.examnotes.net ***Use of inserted table and deleted table in the trigger can help you.
According to BOL
two special tables are used in trigger statements: the deleted table
and the inserted table. SQL Server 2000 automatically creates and
manages these tables. You can use these temporary, memory-resident
tables to test the effects of certain data modifications and to set
conditions for trigger actions;
Look at books online for more details and samples.
Or
Post your table with DDL and data and describe your problem.
Regards
Amish

Compare timestamps then delete

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

Quote:

Originally Posted by JReneau35

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

delete ... where datediff(mm,datefield,getdate()) > 6|||

Quote:

Originally Posted by ck9663

delete ... where datediff(mm,datefield,getdate()) > 6

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.|||

Quote:

Originally Posted by JReneau35

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.

datediff() gets the difference between the start data and end date. the "mm" signifies you're trying to get the difference expressed in number of months. getdate() is a function that returns the system date.

essentially, you're deleting the record if the difference between the datefield (content of your field) and the system date is more then 6 months ... if you need to include 6 months and older, do a "=>" instead

Compare tables from one server/database to another

Hello,
I know for mysql there is a tool that compares tables (like a windiff tool).
Is there anything for MS SQL?
thanks
WilfridThere's no built-in compare tool that comes with SQL Server. You can script
objects and compare with a text file diff tool like Windiff or
BeyondCompare.
There are also third-party tools available like SqlCompare
(http://www.red-gate.com/products/sql_compare/index.htm). If you have
Visual Studio 2005, the CTP3 of the Team Edition for Database Professionals
(http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx)
has a schema compare feature, among others.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:449bbb8e$0$31655$636a55ce@.news.free.fr...
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff
> tool).
> Is there anything for MS SQL?
> thanks
> Wilfrid
>|||You could try ApexSQL's SQLCompare tool. Try the 30 day fully functional
evaluation version.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:449bbb8e$0$31655$636a55ce@.news.free.fr...
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff
> tool).
> Is there anything for MS SQL?
> thanks
> Wilfrid
>|||Wilfrid -- xSQL Software has a great and free comparison and
synchronization tool that you can get from
http://www.x-sql.com/download.aspx (the free edition supports smaller
databases) -- also an sdk that allows you to integrate the comparison
and synchronization functionality in your application is available.
CJK
Wilfrid wrote:
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff tool).
> Is there anything for MS SQL?
> thanks
> Wilfrid|||Wilfrid,
The open-source SchemaCrawler tool will do what you need. SchemaCrawler
outputs details of your schema (tables, views, procedures, and more) in
a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can
also output data (including CLOBs and BLOBs) in the same plain-text
formats. You can use a standard diff program to diff the current output
with a reference version of the output. SchemaCrawler can be run either
from the command line, or as an ant task. A lot of examples are
available with the download to help you get started.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party jars are required.
Once you get familiar with SchemaCrawler's Java API, you can even write
plug-ins that will automatically generate the scripts that you need.
Sualeh Fatehi.