compre data row by row
- Hi friendswe have a table which is specific to our application.we change data in this table every time we release new builds.what i want to know is there any way compare 2 tables (which are same structure) row by row.for exampleTable a:-Col1 Col2--- -----1 'karl'2 'gin'Table b:-Col1 Col2--- -----1 'karl'2 'gin1'Here i want 2nd row to be returned bcoz col2 value is different.i know i can do something likeselect b.* from b,a where a.col1<>b.col1 and a.col2<>b.col2but what if table's have 20 columns each?is there any way better to compare or the only way is my abv example.I even tried likeselect * from b exists(select * from a)but i'm not sure it is getting what i want??Many thanks for ur ideas
-
If you are using SQL 2000 then you could easily do
select * from
a
inner join
b
on
a.col1 = b.col1
where
CHECKSUM(a.*) != CHECKSUM(b.*)
The checksum function is like a CRC value which if they are equal means the data is equal, if they are not then the data was changed.
Otherwise you will have to do a WHERE a.Col2 != b.Col2 OR ... so you can check each field except the joining field.
-
-
Hi
i tried like following
select a.* from
task a
inner join
tracker..task b
on
a.taskid = cast(b.taskid as varbinary(5))
where CHECKSUM(a.*) != CHECKSUM(b.*)
but it is giving me error
Line 13: Incorrect syntax near '*'
(actually these 2 tables r on different collation)
-
I didn't think that would stop you. You are suing SQL 2000? And if so are your databases compatability 8 or 7? I will have to test otherwise but this sounds like it doesn't like something you typed like the word CHECKSUM or something else.
-
Yes,i am using sql2000.
when i remove where condition its working fine.
but i get error when i add checksum!!
no i did not misspel anything
-
First check the database compatibility level of each db. If set to 7 or lower it may not allow the syntax but don't change unless you are sure it will not have an effect of the data, the other objects, or the accessing tools. This is a theory thou.
If fine then post the DDL (the CREATE TABLE statement, this can be gotten in EM, right click the table and choose copy, then paste into QA or elsewhere) for both DBs so I can test similar here. I don't think there is anything that CHECKSUM woudl have issues with.
-
for both databases the compatibility level is 80.
for the table in Tracker database
CREATE TABLE [dbo].[Task] (
[Taskid] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Taskname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fk_catid] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EastimateDuration] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Descr] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Comments] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaskPriority] [tinyint] NOT NULL ,
[calc_priority] [tinyint] NULL ,
[Entrydt] [datetime] NOT NULL ,
[Status] [tinyint] NULL ,
[complete] [tinyint] NULL ,
[buildver] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[snapshot] [image] NULL ,
[chargeable] [bit] NULL ,
[not2bdone] [bit] NULL ,
[targetdt] [datetime] NULL ,
[rowactive] [tinyint] NOT NULL ,
[rowid] [row_id] NULL ,
[unqid] [unq_id] NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
for the table in proj_tracker db
CREATE TABLE [dbo].[Task] (
[Taskid] [varchar] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[Taskname] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[fk_catid] [varchar] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[EastimateDuration] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[Descr] [varchar] (2000) COLLATE Latin1_General_CI_AS NOT NULL ,
[Comments] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[TaskPriority] [tinyint] NOT NULL ,
[calc_priority] [tinyint] NULL ,
[Entrydt] [datetime] NOT NULL ,
[Status] [tinyint] NULL ,
[complete] [tinyint] NULL ,
[buildver] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[snapshot] [image] NULL ,
[chargeable] [bit] NULL ,
[not2bdone] [bit] NULL ,
[targetdt] [datetime] NULL ,
[rowactive] [tinyint] NOT NULL ,
[rowid] [row_id] NULL ,
[unqid] [unq_id] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
many thanks for ur time
-
Ok, I will check when I get to the office tomorrow to see if I can duplicate.
-
Cheers Antares686
-
you could of course save yourself the time and buy an application that can do it all for.... check out http://www.dbghost.com for data, schema comparisons and a whole lot more.
-
Two points over CHECKSUM.
1. it does not work with image data type
2. it can take * , not a.*
for binary comparision use BINARY_CHECKSUM
Try This.
select a.* from Tracker..task a
inner join proj_tracker..task b
on a.taskid = b.taskid
where
CHECKSUM(a.Taskid, a.Taskname, a.fk_catid, a.EastimateDuration, a.Descr, a.Comments, a.TaskPriority, a.calc_priority, a.Entrydt, a.Status, a.complete, a.buildver, a.chargeable, a.not2bdone, a.targetdt, a.rowactive, a.rowid, a.unqid )
!=
CHECKSUM(b.Taskid, b.Taskname, b.fk_catid, b.EastimateDuration, b.Descr, b.Comments, b.TaskPriority, b.calc_priority, b.Entrydt, b.Status, b.complete,b.buildver, b.chargeable, b.not2bdone, b.targetdt, b.rowactive, b.rowid, b.unqid )
-
Weirdly enough, the CHECKSUM and BINARY_CHECKSUM functions do not take table aliases ( or names ) !?
For not having to type all the columnnames derived tables could be used.
select a.*, b.* from
(select col1, checksum(*) as chksum from a) a
join
(select col1, checksum(*) as chksum from b) b
on a.col1 = b.col1
where a.chksum <> b.chksum
/rockmoose
You must unlearn what You have learnt -
Interesting enough it will not accept alias.* but will accetp alias.colname. However I notice there is a couple of columns difference between the two so you may want to use a combination of the last two ideas. You might even want to create a view of table A so you have the same number of columns as b and in the same order then use the 2nd replacing the table A with view A instead.
-
Maybe this isn't as cool as CHECKSUM(), but couldn't you also do some sort of outer join and test for nulls?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply