SQL Server 2000
I will to compare a normal table with a replicated audit table having
identical columns. I wish to report on the differences between the
data.
How can I loop though a "column list" in TSQL rather than explicitly
naming each column to compare ?
Thanks,
Craig 11 17638
Do you just want to know if there IS a difference or what sort of
difference ?
--For the Whole Table
SELECT checksum_agg(bi nary_checksum(* )) from SomeTable
--For just the rows
Select binary_checksum (*) from SomeTable
If you need more granualarity, then it would be more work.
HTH, Jens Suessmeyer.
Jens,
This is not 100% reliable. If the checksum functions are
different, there must be a difference in the tables, but the
converse is not true. The checksum functions can return the
same values on different data, like any hash function.
The only sure way is to compare column by column values.
Steve Kass
Drew University
Jens wrote: Do you just want to know if there IS a difference or what sort of difference ?
--For the Whole Table SELECT checksum_agg(bi nary_checksum(* )) from SomeTable
--For just the rows Select binary_checksum (*) from SomeTable
If you need more granualarity, then it would be more work.
HTH, Jens Suessmeyer.
Yes I agree with you, definitely. But as I said, to have more
granularity and more reliabilty you have to use another mechanism which
causes more coding. but as I said, I am with you.
Jens Suessmeyer.
Thanks for the reply.
I have to interrogate each column and determine which is different and
then report on the difference. I was thinking more in the lines of
looping through the columns collection somehow without having to know
the name of each column ...
Thanks again !
(cs******@dwr.c om) writes: SQL Server 2000
I will to compare a normal table with a replicated audit table having identical columns. I wish to report on the differences between the data.
How can I loop though a "column list" in TSQL rather than explicitly naming each column to compare ?
You can get the columns for a table with
SELECT name FROM syscolumns WHERE id = object_name('tb l')
I would suggest that it is best to generate the SQL statement from client
code, since client languages are better apt for string manipulation. You
can build SQL strings in T-SQL as well, and exeute them with EXEC() or
sp_executesql, but this is bulkier.
If you want to be static, one option is to use a client-language to
generate a stored procedure.
Keep in mind that you cannot just say:
SELECT ...
FROM tbl a
JOIN audit_tbl b ON a.keycol = b.keycol
WHERE a.col1 <> b.col1
AND a.col2 <> b.col2
...
Correct is:
WHERE (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)
AND
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
On Fri, 14 Oct 2005 21:51:02 +0000 (UTC), Erland Sommarskog wrote:
(snip) Keep in mind that you cannot just say:
SELECT ... FROM tbl a JOIN audit_tbl b ON a.keycol = b.keycol WHERE a.col1 <> b.col1 AND a.col2 <> b.col2 ...
Correct is:
WHERE (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) AND
Hi Erland,
This can be (slightly) simplified to
WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR
NULLIF (b.col1, a.col1) IS NOT NULL)
AND
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes: This can be (slightly) simplified to
WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR NULLIF (b.col1, a.col1) IS NOT NULL) AND
Simplified in the sense "few characters to type, yes". Else I'm not
sure that I find it simpler. Or it is just that it's Friday night after
a hard and long working week...
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
On Fri, 14 Oct 2005 22:14:32 +0000 (UTC), Erland Sommarskog wrote: Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes: This can be (slightly) simplified to
WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR NULLIF (b.col1, a.col1) IS NOT NULL) AND
Simplified in the sense "few characters to type, yes". Else I'm not sure that I find it simpler. Or it is just that it's Friday night after a hard and long working week...
Hi Erland,
Well, okay. It's an acquired taste, I'll have to grant you that. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
SELECT X.a, X.b, ... X.z
FROM
(SELECT a, .b, ... z
FROM AuditData
UNION ALL
SELECT a, b, ... z
FROM BaseData)
AS X (a, b, ... z)
GROUP BY X.a, X.b, ... X.z
HAVING COUNT(*) < 2;
or if we had SQL-92 set operators, you could write:
SELECT *
FROM (SELECT 'B', * FROM BaseData)
EXCEPT
(SELECT 'A' , * FROM AuditData);
or
SELECT *
FROM (SELECT 'A', * FROM AuditData)
EXCEPT
(SELECT 'B' , * FROM BaseData); This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bill |
last post by:
In an effort to improve the speed of queries against my main table,
I'll be indexing a column whose data type is varchar(50).
Would I be better off (better performance) if I changed the column's
data type to some numeric type? I would have to update the column's
data to accomodate this, but I would do it if this offers a
performance gain.
-- Bill
|
by: Carl |
last post by:
Hi,
I have found a way to map attributes (columns) to column headings. But
this runs really slow. Is there a way to improve it?
Thanks,
Carl
<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type="text/xsl" href="stylesheet.xslt"?>
|
by: Dr. Ann Huxtable |
last post by:
Hello All,
I am reading a CSV (comma seperated value) file into a 2D array. I want
to be able to sort multiple columns (ala Excel), so I know for starters,
I cant be using the array, I need something more sophistictated like a
vector of row objects.
The data is of this format (same number of columns for each row):
A, x, y, z, ...
|
by: Paul |
last post by:
Assume you have two varchar (or Text) columns named L and U which are
identical except that the charset for L is latin1 and the charset for
U is utf8. All the records in L and U are identical in terms of
content, consisting of only 7 bit ASCII characters. Both columns have
indexes of the same type (e.g. assume Unique indexes if you want).
Here's my question: Will the fact that column U has a utf8 charset
make select queries run slower on...
|
by: DMAC |
last post by:
If i want to split a computed column into two or more columns based on
the the length (its a varchar) of the computed column, how often will
sql server determine what the computed column is? roughly along the
lines of
select comp_col,'comp_col 2'=
case when datalength(comp_col)<=100 then comp_col
else left(comp_col,99) + '~' end,
datalength(comp_col)
from aTable
| |
by: neelu |
last post by:
I have four columns in listview.
SR Number Part# DES Qty Weight
59 9410106800 Nut 10 0.03
60 90304ge800 Helmet 5 0.325
61 9635439604 Cap 15 0.421
62 25340h1245 Shoes 2 0.001
I want that when I click on column part# it sort the list View
|
by: juliane26 |
last post by:
Even so it should not be there, it sometimes is:
somebody wants to rename a column.
Today we do unload / recreate table / load using our own db utilities
to do so.
Now we evaluate Object Comparison to be used for our general admin
purposes.
Questions:
1) Is it possible to rename columns e.g. using the compare masks; and
|
by: kjmatthews |
last post by:
I am designing a site but only have access to a Mac, equipped with
Virtual PC. On IE6 is appears that, on some pages of this site, the
navigation does not appear. I have put up a test case:
http://digitalovertone.com/examples/iedisappear.html. I have stripped
most of the irrelevant CSS from this page.
Please observe: on loading the page, the navigation and the words "some
text" should appear in the left column. They do not (at least on...
|
by: yoyo |
last post by:
So I'm trying to make an application that currently works with MySql,
Postgre, etc... work with DB2.
THe problem I have, is, the varchar column only goes to 32k. CLOB goes
bigger, but at a major cost. SELECT DISTINCT, UPPER,LOWER, ORDER BY,
GROUP BY, among other things don't work on CLOB columns.
Without creating many resource robbing UDF's to emulate functionality,
is there something I'm missing? Is there a better way to store larger...
|
by: rhaazy |
last post by:
I need to know how I can format a string in C# to get the current date/
time, so that I can do a comparison against a date time column in MS
SQL Server 2005.
The date/time column in the database is of the followin format:
mm/dd/yyyy hh:mm:ss AM/PM
thanks in advance.
|
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, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |